Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Got this problem I can't figure out how to solve, below is an simplified example of it.
I need to get the numbers from Table 1 into a matrix visualisation. The matrix will be grouped by series and category on row level and by date hierarchy on columns.The subtotals for the series should be the SUM whereas the category should be AVERAGE for any given timeperiod.
Table 1
Date | Series | Category | Value |
01/01/2020 | A | A1 | 50 |
01/01/2020 | A | A2 | 75 |
01/01/2020 | A | A3 | 100 |
01/02/2020 | A | A1 | 60 |
01/02/2020 | A | A2 | 120 |
01/02/2020 | A | A3 | 100 |
This should be the result when doing drill-down to Date.
Series | 01/01/2020 | 01/02/2020 |
A | 225 | 280 |
1 | 50 | 60 |
2 | 75 | 120 |
3 | 100 | 100 |
And this should be the result when drill-up from Date to Month
Series | Jan 2020 |
A | 247,50 |
1 | 55 |
2 | 97,5 |
3 | 100 |
Any ideas?
Chris
Solved! Go to Solution.
Please try this expression, replacing Data with your actual table name. I didn't know if you have a Date table or not, so I just used the Date column in the table, and made Month column in that table to test it out.
Total =
AVERAGEX ( VALUES ( 'Data'[Date] ), CALCULATE ( SUM ( 'Data'[Value] ) ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression, replacing Data with your actual table name. I didn't know if you have a Date table or not, so I just used the Date column in the table, and made Month column in that table to test it out.
Total =
AVERAGEX ( VALUES ( 'Data'[Date] ), CALCULATE ( SUM ( 'Data'[Value] ) ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat ,
and thanks for your suggested solution. This expression works exactly the way I want it in my simplified example, but when implementing the same expression to the real data, the subtotals are wrong. Have to dig into this further to see what the problem is.
Hi @Anonymous ,
Try these measures
AverageV = Average('Table'[Value])
SUMV = SUM('Table'[Value])
Measure = IF(ISINSCOPE('Table'[Category]),[AverageV],[SUMV])
Regards,
Harsh Nathani
Thanks for your suggestion. There is however a problem with the month total. I would need this to be 55+97,50+100 = 202,50, i.e it should add up the values from the categories rows and not the series row.
Chris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |