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
mlozano
Helper II
Helper II

Error calculating totals in a calculated column

Hi, I am trying to build a calculated column in DAX that will display the correct total not only as the result of the sum of the column, but also as the result of each of the rows. As in the following picture:

 

1.Without filters

 

mlozano_0-1654385375482.png

2.The result should be dynamic based on filters applied from the filter object visual and the result is still correct:

mlozano_1-1654385656515.png

 

 

So far I have built the following code but without the expected result:

 

Total ACT Vol-Column = CALCULATE(
                                                           SUM(Worksheet[ACT Volume]),
                                                                                                             ALLSELECTED(Worksheet)

                                                           )

The result that I am receiving is a static value, which does not change according to the applied filter and also the sum of the column gives me an incorrect value:

 

3.Without filters

mlozano_3-1654386147212.png

4.with filters

mlozano_4-1654386211892.png

 

How can i adjust my code so that my calculated column gets the expected result and behavior.

 

 

 

1 ACCEPTED SOLUTION

Hi,

Download the revised PBI file from here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

No.  A calculated column formula does not recalculate on a change in slicer/filter.  Why do you want a calculated column formula when in fact my measure gives you the right answer?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Doesn't only this measure work

=SUM(Worksheet[ACT Volume])

If it does not hlp, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello, thank you very much for your answer, I already tried it and it doesn't work, I leave you a link to download the pbix file:

 

FILE PBIX 

Hi,

Download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hello, no, a measure does not work for me because I need the grand total to also be replicated row by row and change depending on the application of the filters, in its solution the result of each row is reflected according to its SKU, I want to reflect the sum of the total volume in each row.

 

Like in the image. 

 

The total of each row also corresponds to the total value, and must recalculate its value according to the applied filters.

 

Without filters

 

mlozano_0-1654389497369.png

with filters

mlozano_1-1654389661210.png

 

 

Hi,

Download the revised PBI file from here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.