Forum Discussion
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!
I think this one works amitchandak
AP Invoice Corporate Amount = sumx('Invoice',[Amount] * RELATED('Rates'[USD Conversion Rate]))
3 Replies
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
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)I think this one works amitchandak
AP Invoice Corporate Amount = sumx('Invoice',[Amount] * RELATED('Rates'[USD Conversion Rate]))
