Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping dates and KPIs by category

Hi all,   I need to group the KPI values by the date grouping - for example showing all week calculations / values under Week, Month, etc   Is there a way to achieve the below in Power BI?   ...
Need Help
Show and Tell
  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago

    You're very welcome Anonymous 

     

    I'm not sure I understand your latest question.

    Anonymous wrote:

    Thanks a lot AllisonKennedy that looks like a really good solution. 

    One question please, if we want to show all scopes (Week,Month,Day) in the same table (not single select from DimTD[Scope] filter), is there a way to achieve that?

     

    Thanks again

    Try to use a matrix visual, not a table visual. If you put the DimTD[Scope] in the matrix you can get it to all show in the same table and look almost exactly like your image. You can even put Year above Scope in the columns, and put all your measures in values, and just format the matrix to turn off subtotals and show values as rows.

     

    From my previous post on how to get the configuration right:  

    The last trick is getting the configuration correct. So you will need to ensure you are using a date filter on the page or visual. Then place DimTD[Scope] in columns and all of your measures in Values of a Matrix visualization. Click on the format painter icon in the visualizations pane to turn off totals and turn on values show as rows. 

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous ok, here's one idea you can try; 

I created a new table for your column headings and called it DimTD

Scope
Week
Month
Day
 
Then all you need to do is follow the below format to create a measure for each of your rows (Sales, CPC, Traffic, Spend):
Sales = IF(HASONEVALUE(DimTD[Scope]), IF(SELECTEDVALUE(DimTD[Scope])="Year"&&HASONEVALUE(DimDate[Year]),CALCULATE(SUM(sales[Revenue]),DATESYTD(DimDate[Date])), IF(SELECTEDVALUE(DimTD[Scope])="Month",CALCULATE(SUM(sales[Revenue]),DATESMTD(DimDate[Date])),IF(SELECTEDVALUE(DimTD[Scope])="Week", [WeekToDateSales],""))),"Configuration Error")
 Week To Date isn't a built in DAX function, so view this post for advice on how to create; https://radacad.com/week-to-date-calculation-in-power-bi-with-dax
 
This measure is basically saying, if Scope says 'Week', then calculate WTD, If it says 'Month', calculate MTD, etc. 
 
The last trick is getting the configuration correct. So you will need to ensure you are using a date filter on the page or visual. Then place DimTD[Scope] in columns and all of your measures in Values of a Matrix visualization. Click on the format painter icon in the visualizations pane to turn off totals and turn on values show as rows. 
 
Let me know if you have any issues.
 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks a lot AllisonKennedy that looks like a really good solution. 

    One question please, if we want to show all scopes (Week,Month,Day) in the same table (not single select from DimTD[Scope] filter), is there a way to achieve that?

     

    Thanks again

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      6 years ago

      You're very welcome Anonymous 

       

      I'm not sure I understand your latest question.

      Anonymous wrote:

      Thanks a lot AllisonKennedy that looks like a really good solution. 

      One question please, if we want to show all scopes (Week,Month,Day) in the same table (not single select from DimTD[Scope] filter), is there a way to achieve that?

       

      Thanks again

      Try to use a matrix visual, not a table visual. If you put the DimTD[Scope] in the matrix you can get it to all show in the same table and look almost exactly like your image. You can even put Year above Scope in the columns, and put all your measures in values, and just format the matrix to turn off subtotals and show values as rows.

       

      From my previous post on how to get the configuration right:  

      The last trick is getting the configuration correct. So you will need to ensure you are using a date filter on the page or visual. Then place DimTD[Scope] in columns and all of your measures in Values of a Matrix visualization. Click on the format painter icon in the visualizations pane to turn off totals and turn on values show as rows.