Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rpiboy_1
Resolver I
Resolver I

Fact Value Unit Conversion accounting for different Summarization

I have a Semantic Model that is intended to be consummed by multiple stake-holders to build their own Reports (true self-service BI). To that end I've been trying to make it as user friendly as possible, names that articulate purpose, descriptions, etc. There are several values in my Fact tables where I've picked a default summarization, Average for instnace, but depending upon the end user, Sum or Max or Min might make just as much sense.

 

However, some of these values are in specific units (area to be exact) and therefore if someone wants to see Square Feet versus Square Meters the value(s) need to be converted. I've written some DAX to handle the conversion, however it occured to me, that by doing so the summarization is now implicit by way of the my DAX (SUMX vs AVGX for example).

 

Is there an elegant way in DAX to still give the end user flexibility, in changing the Summarization without having to write multiple new measures? Could I use calcualtion groups?

For reference here is the DAX, pretty straight-forward:

Designed Area (converted) = 
VAR UnitCheck = HASONEVALUE(Units[Name])

VAR UnitFactor = IF(UnitCheck = TRUE(), SUM(Units[Area (SqM/SqFt)]), 1)

VAR ConvertVal = IF(UnitCheck = TRUE(), SUMX('Latest Project Data', UnitFactor * 'Latest Project Data'[Sum Designed Area]), "Invalid Conversion Value")

RETURN
ConvertVal
1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @rpiboy_1 ,

 

As far as I know, each measure has its own hard-coded formula. It is not possible to change the aggregation used by Power BI. For example, if it is a sum, which it can only be, you can use the corresponding aggregation function in the measure

Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Learn

And new group is a grouping of values in a column with custom rules for data points to help you view, analyze, and navigate data and trends in visual objects more clearly.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.