Skip to main content
cancel
Showing results for 
Search instead 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

Reply
AsNa_92
Helper I
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:

charts.png

 

sample data:

DateRate_TypeValue
Dec2023Increase48
Dec2023Decrease12
Jan2024Increase49
Jan2024Decrease14
Feb2024Increase50
Feb2024Decrease15
Mar2024Increase55
Mar2024Decrease14
Apr2024Increase63
Apr2024Decrease18

 

1 ACCEPTED SOLUTION
Sergii24
Super User
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.

Sergii24_0-1715760475657.png
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:

Sergii24_1-1715760614219.png


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):

Sergii24_2-1715760759626.png


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!

 

View solution in original post

2 REPLIES 2
Sergii24
Super User
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.

Sergii24_0-1715760475657.png
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:

Sergii24_1-1715760614219.png


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):

Sergii24_2-1715760759626.png


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 @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... 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors