topic Fill in a measure value to all rows in a new measure by group in DAX Commands and Tips

Fill in a measure value to all rows in a new measure by group

RandomWalker — Wed, 17 Jan 2024 20:06:59 GMT

Hi Members,

I'm working on a dashboard where I need to compute the ratio between two measures. To be more specific, I'd like to

get the ratio between Average Amount and Ref, both are measures, as shown in the screenshot. For each FY, the Ref

value is the one in the WI category (indicated by arrows). I managed to get the WI category value show up

in the Ref measure, but I am having difficulties to populate the value by FY group. My case is a bit complicated because

I need to use slicers on FY/Size/Category to showcase the ratio in various scenarios. In the screenshot, Total Amount,

Total Order, Average Amount are all measures.

I did a little bit of search and came across three cases that are very close to my situation:

However none of the solutions work in my case. It'd be appreciated if experts here could help me find a solutuon.

Thank you for your time!

-------------------------------------------

Sample PBIX file: filter_test.pbix

Sample data: Sample.csv

Re: Fill in a measure value to all rows in a new measure by group

RandomWalker — Wed, 17 Jan 2024 23:31:52 GMT

I think I'm getting there but not quite yet. Inspired by this post, it seems that with this Ref measure:

Ref = CALCULATE( [Avg Amount], FILTER( ALLSELECTED(Sample), Sample[Category] = "WI" ), VALUES(Sample[FY]) )

I am able to propagate the value of category WI to the rows by FY:

However, this measure does not play well with the Category slicer. For example, if OT is selected in the silcer, Ref column is empty and the ratio is not correct:

Re: Fill in a measure value to all rows in a new measure by group

RandomWalker — Thu, 18 Jan 2024 07:54:07 GMT

Note: the following is not strictly a DAX solution, it's just a workaround based on data model change.

After some thougts, I decided to change the data model by adding two more columns for WI category Order and Amount data in each group. (You could find the original data in the link above)

With this structure, it becomes straightforward to just compute Total Amount, Total Order, and Ave Amount measures using these DAX expressions:

Tot Amount = SUMX( KEEPFILTERS(VALUES(Sample[Amount])), CALCULATE( SUM(Sample[Amount]) ) ) Tot Order = SUMX(KEEPFILTERS(VALUES(Sample[Order])), CALCULATE(SUM(Sample[Order]))) Avg Amount = VAR _amt = SUMX( KEEPFILTERS(VALUES(Sample[Amount])), CALCULATE(SUM(Sample[Amount])) ) VAR _order = SUMX( KEEPFILTERS(VALUES(Sample[Order])), CALCULATE(SUM(Sample[Order])) ) RETURN DIVIDE( _amt, _order)

Then I just create the same measure (with the WI category data) for the added wOrder and wAmount columns in the new data model.

With this data structure modification, the calculation of ratio becomes trivial and the results plays very well with slicers:

Re: Fill in a measure value to all rows in a new measure by group

Anonymous — Thu, 18 Jan 2024 09:03:39 GMT

Hi RandomWalker

Thanks for sharing!

Best Regards,

Jayleny