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 a transaction table which is like this

Customer IDInvoice IDCustomer GroupProduct Group...Line Sales
001001Group 1Stationary...10
001002Group 1IT...54
005003Group 2Stationary...5
..................

 

I then have a global table which summarizes the total sales by customer and product group like this

Customer GroupProduct GroupSales
Group 1Stationary10000
Group 1IT2000
Group 1Office Furniture500
Group 2Stationary5000
Group 2IT5000
Group 2Office Furniture1000

 

I would like to product a dynamic graphic which, when I select a particular customer it shows me a graph like this:

 

 

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] )
    )

     

     

2 Replies

  • 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] )
    )