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.
I have generated a Matrix visual, total column is added at end of visual by default but I need the total to be a custom column not the auto generated one.
Since I am generating 2 saperate tables and making a union out of it, so I need totals of two tables saperately.
Input Data
Date | Amount |
03/01/20 | 100 |
04/01/20 | 50 |
05/01/20 | 70 |
06/01/20 | 150 |
07/01/20 | 170 |
08/01/20 | 120 |
09/01/20 | 90 |
10/01/20 | 80 |
Following formula I am using to generate columns:
Second Appraoch created a measure
Here I am getting data in summary but its a wrong value, I need summary to be 170 instead of 660.
I am sure their should be a way.
Thanks in advance for help.
Hi @siva_powerbi ,
Based on your description, the column2 you created does not meet your calculation requirements.
The summary field is at the same level as the month field, so the summary field does not calculate the sum of "OCT" and "SEP".
You need to use the all function to clear the filters in the table.
Try to create measure like this:
Measure =
var summary = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Column 2]in {"oct","sep"}))
var in_month = SUM('Table'[Amount])
return IF(MAX('Table'[Column 2])="summary",summary,in_month)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@V-lianl-msft Thank you very much for the solution, its working.
Can you please help me with one more functionality.
In matrix visual that I have, their are rows that expand up to 5 levels, iwith your formual I am getting grand totals at all levels but I need the total to be changing as the level is expanded that is sum should be correct as the level is expanded or colapsed.
Can you please help me.
Thanks
@siva_powerbi , Not very clear on the approach, You can create measures with help from time intelligence and date table like
//This month (Oct selected in slicer) and last month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |