cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Best way to display a measure calculation

Hello,

I have a fact table that I put a simplified view of below. My column "Value" is the one I use to display one of the 3 measures in the "Measure" Column with a slicer. So I can select either Quantity, Amount, or UOM to switch between the 3 options.

I have another dimension table connected to this table by the product column. This column has a factor that I can use to multiply by the Value column in the fact table, but only by the Quantity rows. So basically just a multiplication of the factor by the quantities which will give me the calculatio of a 4th measure.

For that I created a new measure as follows:

Value_Quantity_Converted =
VAR _quantity = CALCULATE (
SUM ( MyTable[Value] ),
MyTable[Measure] = "Quantity",
ALL( MyTable[Measure] )
)
RETURN
SUMX ( MyTable, _quantity  * RELATEDConversionFactorTable[Factor] ) )

My question is: what is the best way to display this calculation in my report?

It cant be displayed as a part of the Measure slicer to switch between Quantity, Amount, & UOM, would be great to have a 4th option for Quantity Converted but I believe it is not possible.

So I just need advice on how I should approach this and what is the best way to display this. Thank you.

PS. The conversion is only a conversion from Quantity, I thought of an option to display a clickable button when Quantity is selected to show the conversions. But also not sure how to approach this.

Fact

 Date Product Country Measure Value Jan-24 A US Quantity 10 Jan-24 B US Quantity 5 Jan-24 C US Quantity 25 Jan-24 A US Amount 100 Jan-24 B US Amount 55 Jan-24 C US Amount 125 Jan-24 A US UOM 500 Jan-24 B US UOM 250 Jan-24 C US UOM 1000

Table with factors:

 Product Factor A 5 B 50 C 10
1 ACCEPTED SOLUTION
Super User

@aerizk If I understand correctly, try using a disconnected table that contains your 4 values. You can then use the MAX of that column in your measure and then perform the appropriate calculation. So like:

``````Measure =
VAR __Measure = MAX('DisconnectedTable'[Measure])
VAR __Result =
SWITCH( __Measure,
"Value_Quantity_Converted", [Value_Quantity_Converted],
SUMX(FILTER('Fact'[Measure] = __Measure), [Value]
)
RETURN
__Result
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@aerizk If I understand correctly, try using a disconnected table that contains your 4 values. You can then use the MAX of that column in your measure and then perform the appropriate calculation. So like:

``````Measure =
VAR __Measure = MAX('DisconnectedTable'[Measure])
VAR __Result =
SWITCH( __Measure,
"Value_Quantity_Converted", [Value_Quantity_Converted],
SUMX(FILTER('Fact'[Measure] = __Measure), [Value]
)
RETURN
__Result
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks a lot @Greg_Deckler I followed the same approach with a minor difference. I created 4 different measures to represent each measure then used the disconnected table along with the new measure to switch between the 4.

NEWVALUE =
VAR __Measure = MAX(MEASURETABLE[Measure])
VAR __Result =
SWITCH( __Measure,
"Quantity", [VALUE Quantity],
"Amount", [VALUE Amount],
"UOM", [VALUE UOM],
"CONV", [VALUE CONV],
)
RETURN
__Result
```Measure =
VAR __Measure = MAX('DisconnectedTable'[Measure])
VAR __Result =
SWITCH( __Measure,
"Value_Quantity_Converted", [Value_Quantity_Converted],
SUMX(FILTER('Fact'[Measure] = __Measure), [Value]
)
RETURN
__Result```

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors