Sum based on calculation groups and multiple filter selection

[wladi]

Hello guys,

I have created a calculation group with a logic of dynamic calculation for ABC classification with follow RETURN:

...

VAR Tab_ABCTabInArtikel =

    FILTER(

        CROSSJOIN(

            Tab_KumProzent,

            ABC_Tab

        ),

        AND(

            [@KumProzent] > ABC_Tab[ABC_Untergrenze],

            [@KumProzent] <= ABC_Tab[Obergrenze]

        )

    )

RETURN

    CALCULATE(

        SELECTEDMEASURE(),

        KEEPFILTERS(Tab_ABCTabInArtikel)

    )

Now I try to calculate the target inventory value according to parameters (sliser)

I have create follow measure:

VAR ABCKlasse = SELECTEDVALUE(ABC_Tab[ABC])

VAR Verbrauchsdauer =

    INT(

        (CALCULATE(

        MAX(Artikelposten[Buchungsdatum]) - MIN(Artikelposten[Buchungsdatum]),

        ALL(Artikelposten)

        ) + 1)

        / 7 * 5

    )

VAR Result =

SWITCH(TRUE(),

        ABCKlasse = "A", DIVIDE([hm_Verbrauchswert], Verbrauchsdauer) * AX[AX-Wert],

        ABCKlasse = "B", DIVIDE([hm_Verbrauchswert], Verbrauchsdauer) * BX[BX-Wert],

        ABCKlasse = "C", DIVIDE([hm_Verbrauchswert], Verbrauchsdauer) * CX[CX-Wert],

        ABCKlasse = "N", [hm_Bestandswert] / 100 * (100 - 'Potenzial ND %'[Potenzial ND %-Wert])

    )

RETURN

Result

Now it works best in a bar chart, but the table doesn't show the sum of the measure 'target inventory':

The card visual shows sum if only one value of ABC-filter is selected. But by more as one selected

value, the card visual shows now sum calculation:

I hope you can help me!

[wladi]

I have solved my problem where I used the join table for sumx() funktion in RETURN of calculation group:

But now, if I want to calculate simple calculation by using of calculation groups as filter, I get crazy result 😂

[v-jianpeng-msft]

Hi, @wladi 

Thank you very much for your reply and at the same time thank you for sharing the solution. As for this calculation group being used as a filter causing the result to be incorrect, I guess it might be related to your external context.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[wladi]

Hi Jianpeng,

thank you for your suggestion.

Unfortunately I can not use column calculation because the ABC-Calculation should be carried out dynamically.

For this I have added the calculation group to the page filter:

How can I attach the PBI file with sample data? 

[v-jianpeng-msft]

Hi, @wladi 

Thank you very much for your reply. You can share your PBIX files via Google drive or SharePoint. Please be careful not to include private data. When you use Google Drive, set up anonymous verification.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Hi, @wladi 

I'm trying to create a scenario similar to yours as shown in the image below:

If you need to show totals for this column, you can do it in a few ways:

Method one, establish a measure for this column:

Method 2, set this column to sum:

Method 3, you can use the ISINSCOPE method to tell if the current row is a total row, and if so, then sum.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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