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 th...
Need Help
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

    • Pjaen's avatar
      Pjaen
      Frequent Visitor
      7 years ago

      Hi Anonymous

      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?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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