Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Different Calculation for Different Row Levels in Matrix Visual

Hi all,   Below is a simplified example of the table that I have and the result that I got.     What I'm trying to do is, instead of adding the Actual values of all Measures in one sto...
Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey Anonymous ,

    maybe this measure: provides what you are looking for:

    Measure 2 = 
var isinScopeStore = ISINSCOPE('new 18'[STORE] )
return
IF( isinScopeStore
    , CALCULATE( SUM('new 18'[ACTUAL] ) )
    , COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                VALUES( 'new 18'[STORE] )
            , "noOfYes" , CALCULATE( SUM( 'new 18'[ACTUAL] ) )
            )
            , [noOfYes] = 3
        )
    )
)

    The result based on your sample data:

    Hopefully, this provides what you are looking for.

    Regards,
    Tom

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi TomMartens 

 

Here's the sample data that I use:

 

CHAIN,STORE,MEASURE,ACTUAL
Tesco,Tesco 1,A,1
Tesco,Tesco 1,B,0
Tesco,Tesco 1,C,1
Tesco,Tesco 2,A,1
Tesco,Tesco 2,B,1
Tesco,Tesco 2,C,1
Lidl,Lidl 1,A,1
Lidl,Lidl 1,B,1
Lidl,Lidl 1,C,1
Lidl,Lidl 2,A,0
Lidl,Lidl 2,B,0
Lidl,Lidl 2,C,0

 

MEASURE column is not a DAX measure. It's just a list yes/no questions, but to simplify I use A, B, C. Whereas ACTUAL is the answer of the question under MEASURE column (1 for yes and 0 for no). 

 

The expected result on the chain level is the sum of values on the store level.

 

TIA!

TomMartens's avatar
TomMartens
Super User
3 years ago

Hey Anonymous ,

maybe this measure: provides what you are looking for:

Measure 2 = 
var isinScopeStore = ISINSCOPE('new 18'[STORE] )
return
IF( isinScopeStore
    , CALCULATE( SUM('new 18'[ACTUAL] ) )
    , COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                VALUES( 'new 18'[STORE] )
            , "noOfYes" , CALCULATE( SUM( 'new 18'[ACTUAL] ) )
            )
            , [noOfYes] = 3
        )
    )
)

The result based on your sample data:

Hopefully, this provides what you are looking for.

Regards,
Tom