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
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.
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.
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...
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)
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.
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]) )
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
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.