Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Natalia_987
Regular Visitor

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. 

 

Natalia_987_0-1657801979256.png

Could you please assist?

Thanks 

Natalia

 

1 ACCEPTED SOLUTION
BarnabasToth
Resolver I
Resolver I

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

View solution in original post

8 REPLIES 8
Natalia_987
Regular Visitor

Thanks a lot. The problem has been solved. 

 

v-rongtiep-msft
Community Support
Community Support

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
Super User
Super User

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:

jdbuchanan71_0-1657806230974.png

 

BarnabasToth
Resolver I
Resolver I

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
Super User
Super User

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.

Unfortunately, I can't see the same numbers: 

Natalia_987_0-1657805453662.png

Natalia_987_1-1657805699669.png

 

Natalia_987
Regular Visitor

Hi, I believe it's not working correctly.

Please take a look at the snip attached below:

Natalia_987_1-1657804068581.png

 

Natalia_987_2-1657804251972.png

 

Thanks 

Natalia

 

jdbuchanan71
Super User
Super User

@Natalia_987 

Try a measure like this.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.