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

[RandomWalker]

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:

• Use a measure value of one row to populate all other rows
• New measure to take the value from 1 row and apply it to all rows in the table
• Divide Measure by its Max value

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

[RandomWalker · Accepted solution]

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:

 

[v-jialongy-msft]

Hi @RandomWalker 

Thanks for sharing!

Best Regards,

Jayleny

[RandomWalker]

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: