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,

 

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

    • Pjaen's avatar
      Pjaen
      Frequent Visitor
      7 years ago

      HI Anonymous

       

      You cant dowload test example  in this link

       

      Thanks

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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