cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alrythmond Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Total calculation not responding correctly when applied with filter

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

Re: Total calculation not responding correctly when applied with filter

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

Alrythmond Regular Visitor
Regular Visitor

Re: Total calculation not responding correctly when applied with filter

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!

Alrythmond Regular Visitor
Regular Visitor

Re: Total calculation not responding correctly when applied with filter

wow it works thank you so much!

Super User
Super User

Re: Total calculation not responding correctly when applied with filter

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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 155 members 1,979 guests
Please welcome our newest community members: