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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ikavaju
Frequent Visitor

Computing totals by summing visible values (based on measure) row by row in DAX

Hi,

 

I have a issue to calculate a measure in a way that it gives the result on row level and on total level in the same measure.

 

I have managed to calculate two different measures, neither of those give the proper putcome.

 

Spend saving potential =
CALCULATE(
SUMX('Table', 'Table'[QUANTITY])
[Material Price saving potential to Category price Avg]  )

 

Spend saving potential option2 =
SUMX(
    VALUES('Table'[Material] ),
    IF([Spend saving potential] >0, [Spend saving potential])
)

 

As can be seen in the picture below, the "Spend saving potential" is the aggregated savings of materials where the material average price is over the correspondedcategory averages. If material prices are under category avg prices, those are blanked out.

 

 

picture1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

The link PBIX file

https://drive.google.com/file/d/19DQW4kovKCk9zn6OkPCqJty0aLc3zkzj/view?usp=sharing

 

 

Thanks if you can solve this!

1 ACCEPTED SOLUTION
andhiii079845
Super User
Super User

Thank you for direct sharing your PBI. I make it more easier for helping.
Try this

 

Combine = 
VAR _cat = CALCULATE(
SUMX('Table', 'Table'[QUANTITY])
*
[Material Price saving potential to Category price Avg]
)

VAR _mat = SUMX(
    VALUES('Table'[Material]),
    IF([Spend saving potential] >0, [Spend saving potential]))
RETURN

IF(HASONEFILTER('Table'[Material]),_cat,_mat)
 

andhiii079845_1-1677870964189.png

 


 





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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ikavaju
Frequent Visitor

Hi,

The proposed solution was good enough for the time being, so I accepted yi as solution.

Thank you @andhiii079845 !

 

andhiii079845
Super User
Super User

I have to look later in the pbi again to answer your question in general you want to combine to different measures which have different filters and one a "group by" per 'Table'[Material] . So you have to decide what you want to show when. 


In other words, I am lacking understanding why "Spend saving potential" does not produce value for total?
And also lacking understanding why "option2" does not give values for matrix rows.

Do you mean no totals of the level of the Category? Can you explain more in detail what do you not understand, please? I think (not 100 % sure) you have blank values in the measure ([Material Price saving potential to Category price Avg]) so he can not give a total back for the category or total. 





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

Proud to be a Super User!




andhiii079845
Super User
Super User

Thank you for direct sharing your PBI. I make it more easier for helping.
Try this

 

Combine = 
VAR _cat = CALCULATE(
SUMX('Table', 'Table'[QUANTITY])
*
[Material Price saving potential to Category price Avg]
)

VAR _mat = SUMX(
    VALUES('Table'[Material]),
    IF([Spend saving potential] >0, [Spend saving potential]))
RETURN

IF(HASONEFILTER('Table'[Material]),_cat,_mat)
 

andhiii079845_1-1677870964189.png

 


 





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

Proud to be a Super User!




Hi,

Thanks for your answer @andhiii079845 

 

In a way, this resolve the case.

However I wonder is there more "sophicticated / simplified" measure, which would automatically calculate desired outcome (one code for rows and also for totals) without using this kind of "workaround" where the code is longer and more complicated.
In other words, I am lacking understanding why "Spend saving potential" does not produce value for total? And also lacking understanding why "option2" does not give values for matrix rows.

 

Thanks for support!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.