Calculation based on multiple slicers as total

[AsNa_92]

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

[Sergii24 · Accepted solution]

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!

[AsNa_92]

Hi @Sergii24 
Thanks it works perfectly 😉

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

https://community.fabric.microsoft.com/t5/Desktop/Specific-series-of-dates-in-line-chart-based-on-da...