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
Anonymous
Not applicable

Calculate summary with average inside

Hello world, 
new to DAX and PBI and full of questions.!
I have this 

Amount Converted= 
[Amount] * AVERAGE('Rates'[USD Conversion Rate])

that works fine with each of the lines , but for aggregated data/summary is wrong (e.g excel summary of the numbers shows 900 usd which is correct, but pbi shows 400usd). I feel there's something wrong with the summarization of the average but do not know exactly what happens. Anyone faced this issue?Any suggestions?
Thank!
 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I think this one works @amitchandak 

 

AP Invoice Corporate Amount = 
sumx('Invoice',[Amount] * RELATED('Rates'[USD Conversion Rate]))

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous 

You have force a row context, else it will again calculate total


Amount Converted=
sumx(summarize(table,table[Level],"_1",[Amount] * AVERAGE('Rates'[USD Conversion Rate])),[_1])

 

Table[Level] is the column or set columns that you can use to get correct row level

Anonymous
Not applicable

Hi @amitchandak , great insight on this type of calcs, didn't know that I could do something like this.
I get what you do but unfortunately it gives me the exact same number with the previous calculation, although I have used a unique ID as level, so the row level seems ok. I have a suspicion that the average is being calculated for the whole column, despite filters applied to it (it is part of a drillthrough report)

Anonymous
Not applicable

I think this one works @amitchandak 

 

AP Invoice Corporate Amount = 
sumx('Invoice',[Amount] * RELATED('Rates'[USD Conversion Rate]))

 

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.