topic Re: Computing totals by summing visible values (based on measure) row by row in DAX in DAX Commands and Tips

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

ikavaju — Fri, 03 Mar 2023 12:47:06 GMT

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.

The link PBIX file

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

Thanks if you can solve this!

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

andhiii079845 — Fri, 03 Mar 2023 19:16:43 GMT

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)

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

ikavaju — Tue, 07 Mar 2023 08:09:23 GMT

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!

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

andhiii079845 — Tue, 07 Mar 2023 09:53:28 GMT

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.

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

ikavaju — Fri, 10 Mar 2023 11:19:08 GMT

Hi,

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

Thank you andhiii079845 !