Formula incl. subtotal calculation connected to filters /slicers

[Anouk7770]

Hello.

I have a complex use case with PowerBI where I need help with. It has to do with formulas with subtotals that should be connected to slicers/filters.
As I understand I cannot use calculated columns as they will not update when I change the filter in a visual / table.

Let's assume I have the Sales and Margin Data for Customers for 2 years (indicated by different columns). If there are sales in both periods, they are called "Matching Pair". This is how the raw data looks like (last column optional, as it has to be calculated from scratch whenever applying filters:

Segment	Country	Customer	Product	Revenues 2020	Revenues 2019	Margin 2020	Margin 2019	Matching Pairs	Sales (price impact)	Sales (price) %
A	China	Customer X	Product 1	39’061	29’616	14’987	11’551	Matching Pairs	30’600	0%
B	China	Customer Y	Product 2	10’000	11’389	7’000	6’247	Matching Pairs	-1	7%
B	US	Customer V	Product 1	52’000	49’471	29’397	33’260	Matching Pairs	-5	31%
C	Germany	Customer Z	Product 2	30’000	1’035	20’000	740	Matching Pairs	0	0%
C	Argentina	Customer W	Product 2		7’059		2’843	Non Matching	17	0%
A	Argentina	Customer U	Product 1	2’309	4’600	935	1’878	Matching Pairs	20	4%
A	US	Customer T	Product 1	4’504		1’706		Non Matching	11	0%
SubTotal				137’875	103’170	74’025	56’519		30’642	41%

The last column is calculated using the following Excel formula (haven't adjusted it yet to DAX as I am not sure yet how to do this using subtotals, filters and still calculating on a row level):

IF([@[Matching Pairs]]="matching pairs";IFERROR(-([@[Margin 2019]]*[@[Sales (price impact)]]*Table[[#SubTotals];[Revenues 2019]]/[@[Revenues 2019]]-([@[Margin 2019]]*Table[[#SubTotals];[Revenues 2019]]))/([@[Revenues 2020]]*((Table[[#SubTotals];[Revenues 2019]])^2)/([@[Revenues 2019]]));0);0)

It includes the Subtotals of Revenue 2019.

I want to create now a table / visual, that for example looks like this:

Country	Sum of Sales (price) %
Argentina	4%
China	7%
Germany	0%
US	31%
Grand Total	41%

And has a couple of filter /slicers options (e.g. product, country, segment). 

As the Sales Price % is calculated on a line item level and involves subtotals, the value is supposed to change, when I change the filters. 

I understand, that when I use a calculated column, dynamic filtering is not possible. And when using a measure, I would need to have the same granularity in the visual as in the raw data (but this I can't do, as my raw data in fact includes many more columns than in the example on top). 

Is there any other solution to this problem? 

Many thanks in advance! 

[v-lionel-msft]

Hi @Anouk7770 ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anouk7770]

Hi Lionel

Thanks a lot! 

The subtotal formula you suggested works perfectly. Still struggeling with the other formula though, I think the issue is still that the measure does not calculate it on a row level when I don't have the same granularity in my displayed table. 

I tried using that formula:

Sales (Price %) =
IF(
MAX(
Sheet2[Matching Pairs])="Matching Pairs",
SUMX (Sheet2,
IFERROR(-
(
Sheet2[Margin 2019] * Sheet2[Sales (price impact)]*[Subtotal of R_2019]/Sheet2[Revenues 2019]-(
Sheet2[Margin 2019]*[Subtotal of R_2019]))/(
Sheet2[Revenues 2020]*(
[Subtotal of R_2019]*[Subtotal of R_2019])/(
Sheet2[Revenues 2019])),
0)),
0)

It doesn't give me the results I was hoping for though. I tried to attach the sample PbIx file but it doesn't work somehow.  

Thanks! Kind regards

[v-lionel-msft]

Hi @Anouk7770 ,

Try to create such a measure.

Measure = 
IF(
    MAX( [Matching Pairs] ) = "Matching pairs",
    IFERROR(
        //Since I did not understand your mathematical calculation logic, here MAX([Margin 2019]) * MAX([Margin 2020]) is just an example.
        MAX([Margin 2019]) * MAX([Margin 2020]),
        0
    ),
    0
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anouk7770]

Hi Lionel

Thanks a lot! But the MAX function will not give me the subtotal (sum of all line items that are not excluded by a filter), no? it will give me just the maximum value, or am I wrong?

Thanks!

Kind regards

Anouk

[v-lionel-msft]

Hi @Anouk7770 ,

Or try this.

First create a few measures to calculate the subtotals for each field, and then reference them in the formula. 

Subtotal of R_2019 = 
SUMX(
    ALLSELECTED(Sheet2),//This subtotal will change as the filters change.
    [Revenues 2019]
)

Subtotal of R_2020 = 
SUMX(
    ALLSELECTED(Sheet2), // This subtotal will change as the filters change.
    [Revenues 2020]
) 

Measure = 
IF(
    //The MAX() function here returns the value of Matching Pairs in the current row, not the maximum value.
    MAX(Sheet2[Matching Pairs]) = "Matching pairs", 
    IFERROR(
        //sample
        [Subtotal of R_2019] * [Subtotal of R_2020],
        0
    ),
    0
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.