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,

 

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