Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zilliox
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
v-lionel-msft
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.

 

 

littlemojopuppy
Community Champion
Community Champion

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

littlemojopuppy
Community Champion
Community Champion

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.

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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

StefanoGrimaldi_1-1609339859254.png

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

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

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




amitchandak
Super User
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.

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

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




littlemojopuppy
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.