Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Difference Between Global Average

Hello,   I am trying to calculate a difference between a customer's proportion of sales in product groups vs the global average proportion for that customer's product group.    For example I have...
Need Help
Tutorial Requests
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous 

     

    Refer to the following DAX:

    Selected Customer =
CALCULATE (
    COUNT ( 'Table'[Customer ID] ),
    ALLSELECTED ( 'Table'[Customer ID] )
)
    / COUNTROWS ( ALLSELECTED ( 'Table' ) )
    Global =
CALCULATE (
    SUM ( 'Table'[Line Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Product Group], 'Table'[Customer Group] )
)
    / CALCULATE (
        SUM ( 'Table'[Line Sales] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer Group] )
    )

     

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous 

 

Refer to the following DAX:

Selected Customer =
CALCULATE (
    COUNT ( 'Table'[Customer ID] ),
    ALLSELECTED ( 'Table'[Customer ID] )
)
    / COUNTROWS ( ALLSELECTED ( 'Table' ) )
Global =
CALCULATE (
    SUM ( 'Table'[Line Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Product Group], 'Table'[Customer Group] )
)
    / CALCULATE (
        SUM ( 'Table'[Line Sales] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer Group] )
    )