Calculations between columns in matrix. Variable columns by filter

[Pjaen]

Hello.

I need to be able to calculate the difference between groups of two columns in an array.

Through a filter I must be able to select 1 or more values of a category, and the result must show the difference between each category and the previous one.

In this example, i need to calculate dif between category 2-1, 3-2, 4-3 and %. In the second example, i need to calculate the same between 3-1

The category has no relation to any date.

The category is sorted alphabetically so the order of the columns is always correct when selecting.

I attach link to pbix test file.

I will be grateful for any suggestion idea, thank you.

[v-shex-msft]

Hi @Pjaen,

You can try to use following measure to calculate difference between selected categories:

Measure =
VAR categoryList =
    ALLSELECTED ( Table[category] )
RETURN
    CALCULATE (
        SUM ( Table[import] ),
        FILTER (
            ALLSELECTED ( Table ),
            Table[category] = MINX ( categoryList, [category] )
        ),
        VALUES ( Table[Costumer] )
    )
        - CALCULATE (
            SUM ( Table[import] ),
            FILTER (
                ALLSELECTED ( Table ),
                Table[category] = MAXX ( categoryList, [category] )
            ),
            VALUES ( Table[Costumer] )
        )

If above not help, please share some sample data for test, you links seems broken.

Regards,
Xiaoxin Sheng

[Pjaen]

Hi @v-shex-msft

Thank you very much for your reply.

I created the measure as you indicated, but the result is not as expected.

What I want to calculate, is the difference of the column "Import" of a category, of the column "Import" of the category immediately to its left.

Attached images, in one, the result obtained by PowerBI with your help, and in another (manual in Excel) the one that I hope to obtain.

Attached again links to pbix file with the example data.

Greetings and thanks.

[v-shex-msft]

Hi @Pjaen,

I add two variables to store current category and previous category to get corresponding value, please try it if it works:

Measure =
VAR currCate =
    MAX ( Hoja1[category] )
VAR prevCate =
    CALCULATE (
        MAX ( Hoja1[category] ),
        FILTER ( ALLSELECTED ( Hoja1 ), [category] < currCate ),
        VALUES ( Hoja1[Costumer] )
    )
RETURN
    CALCULATE (
        SUM ( Hoja1[import] ),
        FILTER ( ALLSELECTED ( Hoja1 ), hoja1[category] = currCate ),
        VALUES ( hoja1[Costumer] )
    )
        - CALCULATE (
            SUM ( Hoja1[import] ),
            FILTER ( ALLSELECTED ( Hoja1 ), Hoja1[category] = prevCate ),
            VALUES ( Hoja1[Costumer] )
        )

BTW, your first snapshot looks like to get the diff between max and min category based on current category, so I force it to calculate diff between max and min category.

Regards,

Xiaoxin Sheng

[Pjaen]

Hi @v-shex-msft

Thanks again for your help.

Now it works fine for me with one exception.
If a customer does not have a sale in a selected category, he does not return zero. Leave the box blank.

This causes it not to return the subtraction and therefore does not show the difference which is also not totalized.

The value in Measure for Costumer "a" and category "2" must be -80, for Costumer "f" and category "2" must be -59, and Total Mesure for category "2" must be -180

Any solution?

[v-shex-msft]

Hi @Pjaen,

It test on my side and my formula can works well on total level. Can you please share a sample to reproduce and test on that scenario?

Regards,

Xiaoxin Sheng

[Pjaen]

HI @v-shex-msft

You cant dowload test example  in this link

Thanks

[v-shex-msft]

HI @Pjaen ,

I testing with your sample and if found it caused with current category.

VALUES ( hoja1[Customer] ) will filter calculation based on current category, but it will also effect by categories who not contains all customers.

When you use current customer list to get records from previous category, it will lost some of records.(category 2 not has 'a, f' customers, it not calculate customers 'a,f' when you comparing with previous category)

Measure:

Measure = 
VAR currCate =
    MAX ( Hoja1[category] )
VAR prevCate =
    CALCULATE (
        MAX ( Hoja1[category] ),
        FILTER ( ALLSELECTED ( Hoja1 ), [category] < currCate )
    )
RETURN
    IF (
        ISINSCOPE ( Hoja1[Costumer] ),
        CALCULATE (
            SUM ( Hoja1[import] ),
            FILTER ( ALLSELECTED ( Hoja1 ), hoja1[category] = currCate ),
            VALUES ( hoja1[Costumer] )
        )
            - CALCULATE (
                SUM ( Hoja1[import] ),
                FILTER ( ALLSELECTED ( Hoja1 ), Hoja1[category] = prevCate ),
                VALUES ( Hoja1[Costumer] )
            ),
        CALCULATE (
            SUM ( Hoja1[import] ),
            FILTER ( ALLSELECTED ( Hoja1 ), hoja1[category] = currCate )
        )
            - CALCULATE (
                SUM ( Hoja1[import] ),
                FILTER ( ALLSELECTED ( Hoja1 ), Hoja1[category] = prevCate )
            )
    )

Regards,

Xiaoxin Sheng