Forum Discussion
Calculation based on multiple slicers as total
- 2 years ago
Hi AsNa_92, PowerBI can automatically calcualte totals for table, matrix and for stacked column chart. Instead, what you're asking requires you to have "Total" as a column value in your data model.
To obtain the result on the right you'd need to add "Rate Type MasterData" table to your semantic model (aka data model) and connect it with your "table" that contains sample data:
The best way to create "Rate Type MasterData" is to do it in PowerQuery: the idea is to get distinct values of "rate_type" from "table" and add a new row where "Rate_Type" is "Total" (you can then add Rank column simply to sort values in order diferent from the alphabetic one):
Once the semantic model is ready, we can create a calcualtion in the following way:Value calculation = VAR _RateType = SELECTEDVALUE( 'Rate Type MasterData'[Rate_Type] ) //obtain currently selected Rate Type RETURN IF( _RateType = "Total", //if RateType is "Total" CALCULATE( //then calcualte sum of Value, but remove any potential filter on Rate_Type (to obtain the real total) SUM( 'Table'[Value] ), ALL( 'Rate Type MasterData' ) ), SUM( 'Table'[Value] ) //when Rate Type is not total, then calculate the sum keeping a filter on Increase/Decrease )
The important point here is to say what you want to calcualte when "Total" is selected (because there is no corresponding value in "table" we say to calcualte value in "table" for all rate_types, which is basically a total)
You can find pbix attached. Feel free to ask for clarifications if needed and good luck!
Hi AsNa_92, PowerBI can automatically calcualte totals for table, matrix and for stacked column chart. Instead, what you're asking requires you to have "Total" as a column value in your data model.
To obtain the result on the right you'd need to add "Rate Type MasterData" table to your semantic model (aka data model) and connect it with your "table" that contains sample data:
The best way to create "Rate Type MasterData" is to do it in PowerQuery: the idea is to get distinct values of "rate_type" from "table" and add a new row where "Rate_Type" is "Total" (you can then add Rank column simply to sort values in order diferent from the alphabetic one):
Once the semantic model is ready, we can create a calcualtion in the following way:
Value calculation =
VAR _RateType = SELECTEDVALUE( 'Rate Type MasterData'[Rate_Type] ) //obtain currently selected Rate Type
RETURN
IF(
_RateType = "Total", //if RateType is "Total"
CALCULATE( //then calcualte sum of Value, but remove any potential filter on Rate_Type (to obtain the real total)
SUM( 'Table'[Value] ),
ALL( 'Rate Type MasterData' )
),
SUM( 'Table'[Value] ) //when Rate Type is not total, then calculate the sum keeping a filter on Increase/Decrease
)
The important point here is to say what you want to calcualte when "Total" is selected (because there is no corresponding value in "table" we say to calcualte value in "table" for all rate_types, which is basically a total)
You can find pbix attached. Feel free to ask for clarifications if needed and good luck!