Calculating the Difference between two averages

[Natalia_987]

Hello!

I would like to ask you for some advices how to calculate one new measure based on column rebate average%, MSE SD%, Total SD% seperately. 

Example based on total SD% column:

1. Average for all chains is 12,6%

2. For chain "A" total SD% is 3.4%, for chain B is 23.3% and so on...

3. I would like to calculate difference between total sd% per each chain and average total sd% ---> first chain "A" --> 3.4% - total average (12.6%). PLease note that this total average changes between period. 

Could you please assist?

Thanks 

Natalia

[Natalia_987]

Thanks a lot. The problem has been solved. 

[v-rongtiep-msft]

Hi @Natalia_987 ,

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

Then there is something in your [Total SD [%]]] measure that is affecting the calculation.  Can you share a sample .pbix with dummy data so we can see the data model?

In my sample file the measure work as expected:

[BarnabasToth · Accepted solution]

Hi there,

You can use this approach (see link for file😞

Difference compared to average =

VAR AverageOfValue = AVERAGE('Table'[Value])

VAR AverageOfTotal = 
CALCULATE(
    AVERAGE('Table'[Value]),
    ALLSELECTED('Table'[Chain])
)

VAR Result = AverageOfValue - AverageOfTotal

RETURN
Result

Using ALLSELECTED changes the average at the total row in case only a subset of chains are selected.

If this answers your question, please mark this as a solution.

Regards,
Barna

[jdbuchanan71]

What does this measure give you?

Total SD % ALLSELECTED =
CALCULATE ( [Total SD [%]]], ALLSELECTED ( 'Lookup_customers (2)'[Chain] ) )

On every row that should give you the 12.6% total.

[Natalia_987]

Unfortunately, I can't see the same numbers: 

[Natalia_987]

Hi, I believe it's not working correctly.

Please take a look at the snip attached below:

Thanks 

Natalia

[jdbuchanan71]

@Natalia_987 

Try a measure like this.

Diff =
[Total SD %] - CALCULATE ( [Total SD %], ALLSELECTED ( 'Table'[Chain] ) )