cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Sum of a measure

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?

Zilliox_0-1609336712792.png

My PBIX file is here

 

Thank you

25 REPLIES 25
Community Support
Community Support

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.

v-lionel-msft_0-1609467976908.png

HASONEFILTER 

 

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.

 

 

Super User I
Super User I

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

Super User I
Super User I

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...

littlemojopuppy_1-1609342162904.png

 

 

I did this after your previous message, but still not the right sum.

 

Solution Sage
Solution Sage

also you have a filter on the visual that counter the filter visual filters. 

StefanoGrimaldi_1-1609339859254.png

 

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

 

Solution Sage
Solution Sage

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. 

StefanoGrimaldi_0-1609339435730.png

 

Super User IV
Super User IV

@Zilliox , can you please tell the logic ?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

littlemojopuppy_0-1609339539998.png

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

 

Zilliox_0-1609340124341.png

 

Solution Sage
Solution Sage

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

Super User I
Super User I

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?

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors