Forum Discussion
Solved
Conditional sum calculation based on filter
Hi, I have a category filter Category Target A All 18-20 B All 20-25 And table in which targets are placed in columns Data1 Data2 All 18-20 All 20-25 A ...
HI AgencyPowerBi,
I'd like to suggest you enter to query editor and unpivot your table columns:Then you can use above in pivot table to simply summary specific column range by filter.
Sample measures:
Sum index 18~20 = CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Column Index] >= 18 && Table1[Column Index] <= 20 && Table1[Index] = MAX ( Table1[Index] ) ) ) Sum index 20~25 = CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Column Index] >= 20 && Table1[Column Index] <= 25 && Table1[Index] = MAX ( Table1[Index] ) ) )Regards,
Xiaoxin Sheng
HI AgencyPowerBi,
I'd like to suggest you enter to query editor and unpivot your table columns:
Then you can use above in pivot table to simply summary specific column range by filter.
Sample measures:
Sum index 18~20 =
CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALL ( Table1 ),
Table1[Column Index] >= 18
&& Table1[Column Index] <= 20
&& Table1[Index] = MAX ( Table1[Index] )
)
)
Sum index 20~25 =
CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALL ( Table1 ),
Table1[Column Index] >= 20
&& Table1[Column Index] <= 25
&& Table1[Index] = MAX ( Table1[Index] )
)
)
Regards,
Xiaoxin Sheng
This is great!
I came to this solution myself. This way I can put a slicer and change category while my values are calculated.
Thanks for this solution!