Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to have the right sum of a measure and I have tried, I think, all the possible solutions I found on internet.
Can someone help me to have the right total, please?
My PBIX file is here
Thank you
Hi @Zilliox ,
The “Total” error in the table or matrix visual is a very common problem, which requires careful analysis of the context of the formula, but I did not find the .pbix file you uploaded, please upload it again.
Or you can refer to the formula.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
And again it's calculating the value over the entire data set. $155.5k * .845 = $131.4k
The real solution is that you should probably start at the beginning with your measures. Rework them to pull in the correct currency conversion rates and commission rates.
Thanks and happy New Year
If you're talking about the Transaction for Commission column, your measure for Com Rate Measure is also summing the commission rate. Change that to AVERAGE as well...
I did this after your previous message, but still not the right sum.
also you have a filter on the visual that counter the filter visual filters.
Proud to be a Super User!
This is for having a better view when I do thee formulas. Once the formulas is correct. Just for having may.
that shouldn't make any difference on the sum. Right?
The total at the bottom isn't a sum. It's the calculation over the entire set of data. Your measure is summing the entire transaction amount and dividing it by the sum of the currency conversion rate. That's why your total is lower than the sum of the individual line items...it's dividing by (11 * .855556)
Is there a way I can make the sum of the listed values?
I understand and you are right
the issue guys its that the visual total row shows a higher number them the sum of the visual table listed for each row: as you can see when you simplify the table it does match up, cause you have some filters in the measures you using filtering visually what you see but the measaure total doenst change.
Proud to be a Super User!
@Zilliox , can you please tell the logic ?
This is for commission calculation.
You have a margin table that define the weight of the "transaction in EUR". If you do more margin the value of the Transaction in EUR is higher. Less margin is less value. This is the column "Transaction for commission". Based on the sum of this column I calculate a percentage for the commission
I hope it is clear
Hi @Zilliox the problem is how you defined the currency conversion to Euros. You're summing the exchange rate. Change the exchange rate to average instead.
Transaction in EUR =
divide(
SUM('Contract Product Code'[Transaction]), AVERAGE(Contract[Currency Rate to EUR])
)
Can you help for making it right?
Just pasted the code for it.
Sorry, didn't see it.
I tried but still the same issue. The total should be 138.698,76
have you checked your filters? I dont know on witch filters combinations you saying should give 138,698.76 but you do have some filters on level page, visual and filters visual that can be where your diference its vs manual calculation
Proud to be a Super User!
Can you share the measure you're using and maybe some data? The values in the screen shot don't match what you pasted just now
I have add the pbix file in my first message. You don't have access?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |