- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Fill in a measure value to all rows in a new m...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

01-17-2024
11:50 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-17-2024
11:50 PM

__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:

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-17-2024
11:50 PM

__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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-17-2024
03:12 PM

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:

Top Solution Authors

User | Count |
---|---|

30 | |

18 | |

15 | |

14 | |

10 |