cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculation based on multiple slicers as total

Hello,

I have a table with date column which I use it in the slicers, I have 3 slicers and a Bar charts. Each Bar chart is linked to each slicers that shows a value based on the selected date.

I want to create a fourth Bar chart that show the total of the selected dates from the 3 slicers.

How can I implement that?

My excpectation:

sample data:

 Date Rate_Type Value Dec2023 Increase 48 Dec2023 Decrease 12 Jan2024 Increase 49 Jan2024 Decrease 14 Feb2024 Increase 50 Feb2024 Decrease 15 Mar2024 Increase 55 Mar2024 Decrease 14 Apr2024 Increase 63 Apr2024 Decrease 18

1 ACCEPTED SOLUTION
Super User

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!

2 REPLIES 2
Super User

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!

Helper I

Hi @Sergii24
Thanks it works perfectly 😉

I posted another challenging question but I didn't get any solution, if you could help me with: