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
Alrythmond
Helper I
Helper I

Total calculation not responding correctly when applied with filter

Hi all,

I have simple questions regarding dax that I am experimenting.

Pardon for my DAX format I am fairly new to dax.

I have this table

tabl1.PNG

 

I created a measure named “Multiply” which is the multiplication of the values of the two columns percent and whole number.

measure1.PNG

As you can see the total for the “Multiply” should display the total for 0.1 up to 1.6 but it displays the multiplication of the total for each of the two rows

 

table2.PNG

 

I have fixed the formula like this

measure2.PNG

 

Now it shows this Which is correct

 

 

table3.PNG

But when I filter them through a slicer it goes wrong. It goes back to the way it was multiplying the total from the 2 columns

table4.PNG

please help me Thank you in advance.

looking forward to all of your replies.

 

 

 

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try these measures:

Whole = SUM('Experiment Table'[Whole Number])

Per = SUM('Experiment Table'[Percent])

Measure = IF(HASONEVALUE('Experiment Table'[Product]),SUMX('Experiment Table',[Whole]*[Per]),SUMX(SUMMARIZE(VALUES('Experiment Table'[Product]),[Product],"ABCD",SUMX('Experiment Table',[Whole]*[Per])),[ABCD]))

Hope this helps.

 


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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Try these measures:

Whole = SUM('Experiment Table'[Whole Number])

Per = SUM('Experiment Table'[Percent])

Measure = IF(HASONEVALUE('Experiment Table'[Product]),SUMX('Experiment Table',[Whole]*[Per]),SUMX(SUMMARIZE(VALUES('Experiment Table'[Product]),[Product],"ABCD",SUMX('Experiment Table',[Whole]*[Per])),[ABCD]))

Hope this helps.

 


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

wow it works thank you so much!

Hi Thank tou for the fast response, I haven't tried your formula but I have a few questions about it

 

what is ABCD for?


I would like to hear out an explanation for your formula so to better understand it as a whole, thank you so much!

You are welcome.  The SUMX() function created a virtual Table with unlimited columns.  The first and second inputs are the Table (the VALUES() function created a table with 1 column and x rows) and Group by (since the Table [first input] can only have one column, one can group only by that column).  The third input is the title of the second column which you want to create in this virtual table.

Please just Google for SUMMARIZE to understand it better.


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.