Calculate Difference Between Global Average

[Anonymous]

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 ID	Invoice ID	Customer Group	Product Group	...	Line Sales
001	001	Group 1	Stationary	...	10
001	002	Group 1	IT	...	54
005	003	Group 2	Stationary	...	5
...	...	...	...	...	...

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

Customer Group	Product Group	Sales
Group 1	Stationary	10000
Group 1	IT	2000
Group 1	Office Furniture	500
Group 2	Stationary	5000
Group 2	IT	5000
Group 2	Office Furniture	1000

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

[v-eachen-msft · Accepted solution]

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

[amitchandak]

@Anonymous 

You can create a category Average and use

https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/td-p/362637

You have an option in Quick measure too