topic Re: Combine DAX measure with units column? in DAX Commands and Tips

Combine DAX measure with units column?

Anonymous — Tue, 04 May 2021 14:26:31 GMT

I have a calculated measure in DAX for Total Sales of various items in the form of:

Sales = SUM(ExtendedPrice)

Each granular row in my data also has a Unit of Measure stored in a separate column named 'UOM' (LBS, Tons, CF, etc) . How do I present the measure 'Sales' and have the appropriate units concatenated to it so it displays as '100 LBS', '300 Tons', '400 CF' etc?

Thanks

Re: Combine DAX measure with units column?

amitchandak — Tue, 04 May 2021 15:07:55 GMT

Anonymous , You have to make sure UOM is part of visual

Sales = SUM(ExtendedPrice) & max([UOM])

Re: Combine DAX measure with units column?

Jihwan_Kim — Tue, 04 May 2021 15:08:09 GMT

Hi, Anonymous

Please correct me if I wrongly understood your question.

Please try something like the below.

Sales =

FORMAT (

IF ( ISFILTERED ( 'Table'[uom] ), SUM ( 'Table'[extendedprice] ) ),

"#,##0.00"

) & " "

& SELECTEDVALUE ( 'Table'[uom] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Combine DAX measure with units column?

Anonymous — Tue, 04 May 2021 18:24:12 GMT

Thanks Jihwan. I aprecaite your effort.

I get a memory error when trying a simliar pattern. I wanted to keep my post simple so I should say that my 'Sum' is actually a CALCULATE(SUM...) with a condition. Also, the numeric part of the result needs to be turned to a STRING before the concatenation can be done with UOM. Not only that but once it is turned into a STRING the measure can't be referenced in subsequent calculations. I was hoping for some smooth way that Power BI would concatenate the units while keeping my result as a number.

Re: Combine DAX measure with units column?

Jihwan_Kim — Tue, 04 May 2021 18:38:12 GMT

Hi, Anonymous

Thank you for your feedback.

If you create a calculation group and format those by using a Tabular editor in Power BI, it will be still a number. The format can be customized row by row, if you want.

If you can share your sample pbix file's link here, I can try to look into it and come up with a more accurate solution.

Thank you.

Re: Combine DAX measure with units column?

Vinodkumarj — Sat, 02 Mar 2024 17:06:53 GMT

Hi Datazen,

Did you find the solution for the question you raised?

Now I am having same requirement in on of my report. If you found solution please share the measure.