Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
I think this one works @amitchandak
AP Invoice Corporate Amount =
sumx('Invoice',[Amount] * RELATED('Rates'[USD Conversion Rate]))
@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]))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |