Forum Discussion
Calculate subtotal based on multiple slicer
Hi, Prabhu_MDU ;
You could try to change the measure as follows:
Payment = CALCULATE ( SUM ( [Payment] ), FILTER ( 'Table', [Company] = MAX ( Slicer1[Group1_Column] ) && [External] = MAX ( Slicer2[Group2_Column] ) && [Process Center] = MAX ( Slicer3[Group3_Column] ) && [State Name] = MAX ( Slicer4[Group4_Column] )))Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Prabhu_MDU ;
According to your description, if you use table to show it ,you could create a measure to calculate sum of state, and create a flag to filter it ; if you use matrix to show it, you only create a flag measure to filter .
Table:
a)create a measure to calculate sum
Populationsum = CALCULATE(SUM([Population]),ALLEXCEPT('Table','Table'[Country ],'Table'[State]))b)create a flag measure
flag =
var _ccountry=SUMMARIZE('slicer',[Country ])
return IF(MAX([Country ]) in _ccountry||MAX([State])=MAX('slicer'[State]),1,0)c)put flag measure into filter.
The final output is shown below:
Matrix:
a)create a flag measure
flag = var _ccountry=SUMMARIZE('slicer',[Country ])
return IF(MAX([Country ]) in _ccountry||MAX([State])=MAX('slicer'[State]),1,0)b)put it into filter.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yalanwu-msft ,
Thanks for your valuable reply and great effort. the solution is exactly what I am looking for, but in your (as per my sample data) example it is static, but in my real scenario it will be dynamic.
You have got the requirement exactly. but how the same can be achieved dynamically. thats the challenge I am facing.
I Cannot define directly as
CALCULATE(SUM([Payment]) , ALLEXCEPT([Company],[External],[Process Center],[State Name]))because the "ALLEXCEPT" columns will be selected by the user using the slicer. So, I need to say define it like this
CALCULAT(SUM([Payment]) , ALLEXCEPT(
SELECTEDVALUE(Slicer1[Group1_Column])="Company",[Company],
SELECTEDVALUE(Slicer2[Group2_Column])="External",[External],
SELECTEDVALUE(Slicer3[Group3_Column])="Process Center",[Process Center],
SELECTEDVALUE(Slicer4[Group4_Column])="State Name",[State Name]))(this is not the right measure, but the idea is the same), here it becomes dynamic based on the selected value from each slicer. I believe my description is even more elaborate for you.
In the above screenshort, there are totally 9 Columns, out of the 9, 8 will be given as a dropdown in slicers as shown in the image, each slicer value is from each different table and they are independent tables (no relationship created), the slicers are set to "Single Selection" mode "On", as per the user choice from dropdown in each slicers, we will get 4 columns, based on this 4 random columns, I need to calculate the sum of "Payment" the last column. please help me to fix this.
Note: I am sorry, unable to attach the sample power Bi,
Hi, Prabhu_MDU ;
You could try to change the measure as follows:
Payment = CALCULATE ( SUM ( [Payment] ), FILTER ( 'Table', [Company] = MAX ( Slicer1[Group1_Column] ) && [External] = MAX ( Slicer2[Group2_Column] ) && [Process Center] = MAX ( Slicer3[Group3_Column] ) && [State Name] = MAX ( Slicer4[Group4_Column] )))Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.