+ Reply to Thread
Results 1 to 8 of 8

Power BI – Get Rate based on Category and Date

  1. #1
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Power BI – Get Rate based on Category and Date

    I have a somewhat complex issue where I need to get the rate based on a category and a date. Not sure if I can go straight to do a measure or need calculated columns to help. My end goal is to get an average of 3.15 for Jul 2018 and 5.5 for Feb 2019. I placed the breakdown of how I got that in Excel file.

    edit: wanted to upload the power bi file but seems like i can't
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Power BI – Get Rate based on Category and Date

    Sorry - I have no idea how you have arrived at these figures:

    Excel 2016 (Windows) 32 bit
    P
    Q
    R
    2
    Average
    Jul-18
    3.15
    3
    Average
    Feb-19
    5.5
    Sheet: Sheet1

    I get this (using PQ):

    Excel 2016 (Windows) 32 bit
    P
    Q
    5
    Date Count
    6
    Jul-18
    1.27
    7
    Feb-19
    1.55
    Sheet: Sheet1

    or this:

    Excel 2016 (Windows) 32 bit
    P
    Q
    5
    Date Count
    6
    Jul-18
    3.56
    7
    Feb-19
    4.33
    Sheet: Sheet1

    Which is the one you want?
    Last edited by AliGW; 11-07-2019 at 09:39 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power BI – Get Rate based on Category and Date

    Why is R2 multiplied by 90%, to change 3.5 to 3.15?

    Assuming that's an error, and you actually want the average of each rate multiplied value for the selected categories, per calendar date, then you can use a couple of measures:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    You can then filter Category items in your visualisation - or if you really want a single measure, then something like:

    Please Login or Register  to view this content.
    Edit: added workbook, with required output in a pivot table, in Q5. You'll notice I've added a Calendar table, and a Category dimension table to the model... these aren't strictly necessary for the above measures to work, but they make reporting easier.
    Attached Files Attached Files
    Last edited by Olly; 11-07-2019 at 10:59 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Re: Power BI – Get Rate based on Category and Date

    AliGW: my excel sheet has a breakdown on how i got those numbers. Olly got it. thank you nonetheless!

    olly: thank you very much once again. yes, i actually want to multiply by figures by 90% at the end and missed it out at R3. but that's ok. i can add it to your measure at the end.
    i don't understand many things in your first measure. is there somewhere i can read up on something similar?
    1. isn't the DAX function suppose to come after the Measure name? why is it VAR CurrentDate? googled it and there is no such DAX function. same goes for VAR EffectiveRateDate.
    2. i actually googled RETURN and may have got the answer. those 2 are variable names you can create in the DAX function? that's amazing.

    i don't have PowerPivot, so i can't seem to see the Category dimension table you mentioned. are you able to copy it to Excel? i understand about the calendar though. Thanks a lot!
    2.

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power BI – Get Rate based on Category and Date


  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Power BI – Get Rate based on Category and Date

    AliGW: my excel sheet has a breakdown on how i got those numbers.
    Yes, I looked at it, but it wasn't clear (to me), which is why I was asking for help interpreting your figures.

  7. #7
    Registered User
    Join Date
    12-21-2014
    Location
    singapore
    MS-Off Ver
    2016
    Posts
    42

    Re: Power BI – Get Rate based on Category and Date

    Olly: thank you.

    AliGW: Olly has solved it for me. so i'm not sure if you want to know out of curiosity. in column I:K, i copied the table from A:C.
    in column L, each of the records will need to pick up the right rates from E:G based on category and date. for eg, cell L3 that has Alpha for 1-Jul-2018 will take the rate from Alpha 1-Jan-2018 of 20% in G2. Bravo will take G3 and Charlie G4.
    in column M, they each need to be multiplied by the Amt in column L.
    in column N, they need to be summed up by days.
    and in cell R3; since i am only interested in Alpha/Bravo/Charlie, it will be the average of those sum for july 2018.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Power BI – Get Rate based on Category and Date

    Yes, it is nice to know - thanks for that!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fix colours of Power Pivot Pie Chart slices based on data category name
    By dp39darren in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2019, 06:17 AM
  2. Replies: 7
    Last Post: 04-24-2018, 12:07 AM
  3. Increasing at Decreasing Rate using POWER and/or SQRT and/or ^
    By capheresy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2014, 08:48 PM
  4. [SOLVED] Power Pivot Summarize Value By Category From Total
    By goss in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-24-2014, 05:52 AM
  5. LOOKUP value based on date and category!!
    By Si902 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2010, 04:18 PM
  6. rate based on date
    By matteu1 in forum Excel General
    Replies: 3
    Last Post: 08-01-2009, 09:58 AM
  7. Find category value based on date range?
    By thewiz1972 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-09-2006, 10:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1