Forum Discussion

Pjaen's avatar
Pjaen
Frequent Visitor
7 years ago

Calculations between columns in matrix. Variable columns by filter

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.

 

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Pjaen
      Frequent Visitor
      7 years ago

      Hi Anonymous

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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