Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

How to make a calculated table dependent on the source's slicer

Hi!

As I've tried to illustrate in the picture below I have created a calculated table (Variances) out of some fields in the main table (Table1). 

 

The formula behind the calculated table is as follows:

Variances = 

    UNION(
       Row("Type";"BUD";
            "Amount";sum(Table1[BUDamount]);
            "Weight";sum(Table1[BUD weight])
        );
        ROW("Type";"Volume";
            "Amount";sum(Table1[_VarAna.Volume]);
            "Weight";0
        );
        ROW("Type";"Mix";
            "Amount";SUM(Table1[_VarAna.Mix]);
            "Weight";0
        );
        Row("Type";"Price";
            "Amount";SUM(Table1[_VarAna.Price]);
            "Weight";0
        );
        Row("Type";"ACT";
            "Amount";sum(Table1[ACT amount]);
            "Weight";sum(Table1[ACT weight])
        )
    )

 

This works perfectly as long as I don't apply filters (slicer) to my report. The problem is that the calculated table disregards my slicer (Table[Year]). I assume I have to apply CALCULATETABLE somehow, and I've tried that (also CALCULATE) in different ways without luck.

 

Any suggestions as to how I can achieve a calculated table that responds to the slicer in Table1?

 

 

 

191217 Tables.jpg

2 REPLIES 2
az38
Community Champion
Community Champion

hi @Anonymous 

if i understand you correct it should be enough to inclue your sum-statements into CALCULATE like

Variances = 

    UNION(
       Row("Type";"BUD";
            "Amount";CALCULATE(sum(Table1[BUDamount]));
            "Weight";CALCULATE(sum(Table1[BUD weight]))
        );
        ROW("Type";"Volume";
            "Amount";CALCULATE(sum(Table1[_VarAna.Volume]));
            "Weight";0
        );
        ROW("Type";"Mix";
            "Amount";CALCULATE(SUM(Table1[_VarAna.Mix]));
            "Weight";0
        );
        Row("Type";"Price";
            "Amount";CALCULATE(SUM(Table1[_VarAna.Price]));
            "Weight";0
        );
        Row("Type";"ACT";
            "Amount";CALCULATE(sum(Table1[ACT amount]));
            "Weight";CALCULATE(sum(Table1[ACT weight]))
        )
    )

after that your slicer from the sae table should affect your visual

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Thanks for your response @az38 !

 

I tried your modification, and unfortunately it works in an opposit direction of what I need. CALCULATE without any filter clears even the row context. I need row context to be preserved, and also have the table recalculated based on the slicer selection. I do believe CALCULATE or CALCULATETABLE is a part of the solution, but with some sort of filter included. Maybe ALLSELECTED / ALLEXCEPT, but I'm not able to figure out which and exactly how to design/write it.

 

I hope this clearifies, and if not please don't hesitate to ask!

 

Thomas

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.