Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating the Difference between two averages

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

 

Need Help
  • BarnabasToth's avatar
    BarnabasToth
    4 years ago

    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

8 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    Anonymous 

    Try a measure like this.

    Diff =
[Total SD %] - CALCULATE ( [Total SD %], ALLSELECTED ( 'Table'[Chain] ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, I believe it's not working correctly.

    Please take a look at the snip attached below:

     

     

    Thanks 

    Natalia

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately, I can't see the same numbers: 

       

  • BarnabasToth's avatar
    BarnabasToth
    Resolver I
    4 years ago

    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's avatar
    jdbuchanan71
    Super User
    4 years ago

    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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot. The problem has been solved.