Measure Help Needed - Totals Sum calculation

[AFRATH34]

Hi All,

I have a question about a measure I'm using to display the total Comms Payment.

The measure works correctly for each individual line item, but for the total, it calculates by multiplying the total eligible commission by the total achieved percentage, rather than summing the individual line items, as shown in the first image below. I need the total to reflect the sum of each Comms Pmt line. I've looked into why this happens, but I haven't been able to find a solution that works.

268,955 * 30% = 80,686
I need sum of  individual line item from comms Pmt
Eligible Commission is a Calculated Column and Achived % is Measure
Comms Pmt i used below formula

MAX Comms Pmt =

SUM('Commission'[Eligible commission]) * [Achieved %]

Please let me know if anyone can assist me with a specific measure to tweaking my existing methodology above to reach this expected total.

I appreciate the assistance as always!

Thank you.
Afrath

[AFRATH34]

Hi @anmolmalviya05 and @Uzi2019 
First, thank you very much for your suggestions. However, if I change the SUMX formula, both the individual values for each row and the total value also change. The output does not meet the expected result.

[Uzi2019]

Hi @AFRATH34 

can you share the result and expected output??

[AFRATH34]

Hi @Uzi2019 ,

Please find the attached PBIX file for your reference along with my expected output.

Kindly review it and share your output based on the provided PBIX file.

https://drive.google.com/drive/folders/1QhkcS8opYdvcyMbUPvrrbiOl-Qvu7vRU?usp=drive_link 

my output shoud be commission payable total $216,152 (each individual line item Total)

Please let me know if anyone can assist me with a specific measure to tweaking my existing methodology above to reach this expected total.

I appreciate the assistance as always!

Thanks,

Afrath

[Ashish_Mathur]

Access Denied message.

[AFRATH34]

Hi @Ashish_Mathur ,

Access has been granted; please try again now

Thanks,

Afrath

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

=Measure = SUMX(VALUES(Bonus[Seller]),[Commission Payable])

Hope this helps.

[AFRATH34]

Hi @Ashish_Mathur ,

It’s working perfectly! Thank you very much!

[Ashish_Mathur]

You are welcome.

[Uzi2019]

Hi @AFRATH34 

There is concept of row context and filter context. you can check the below link for better understanding this concept.

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

You table is doing row wise calculation even your total value is calculated by row. To change  the total value you have to use SUMX function which is filter context. 

so instead of row wise calculation you total sum will be based on total of  column value.

use below formula:

MAX Comms Pmt =

SUMX('Commission','Commission'[Eligible commission]) * [Achieved %])

I Hope I answered your question!

[anmolmalviya05]

Hi @AFRATH34, I hope you are doing well,

To fix the issue where the total for Comms Pmt is not summing individual line items but instead recalculating based on totals, you can modify your measure to calculate the Comms Pmt per row and then sum the result.

Here's an adjusted DAX formula for Comms Pmt that should sum each row's individual value instead of recalculating based on totals:

Comms Pmt =

SUMX(

'Commission',

'Commission'[Eligible Commission] * [Achieved %]

)

Using SUMX ensures that the calculation is done row by row rather than recalculating based on total values, which is why your original measure was resulting in the total Comms Pmt as 268,955 * 30% = 80,686 instead of the sum of individual line items.

This adjusted measure should now provide the expected total for Comms Pmt. Let me know if this works for you!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in