Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |