How to use calculation group to switch visual result between " In Unit" and "In Tonnage"?

[DarkTracer888]

In my powerbi dashboard, I alwasy need to see result both in "Number of Unit" & "Tonnage"(which is number of unit*unit of weight).

And this requirment is needed for all the fact tables I'm working with.

Dimension and Fact table as below.

Fact table

Sales -> Item number, Sales Qty 

Stock on Hand -> Item number, SOH Qty

Current in Transit -> Item number, Transit Qty

Dimentsion table

Product Master -> Item Number, unit of weight - Related to fact table via Item Number

Current solution is to write Unit and Ton measures separately for each calculation(sales, soh, git). 

Current Measure

Sales Unit =sum('Sales'[Sales Qty])

Sales Ton = sumx('Sales',[Sales (u)]*related('Product Master'[Unit of Weight]))

Final Measure to put in visual

Sales =

    Switch(True(),

        [Selection] = "Units", [Sales Unit],// Created a Selction table to use as slicer.

        [Selection] = "Tonnes", [Sales Ton],

        [Sales Ton])

How we achieve this dynamically? So I can have one measure for all my calculations(columns from different tables.)

Item Number	Unit of Weight
1	0.5
2	0.7

Item Number	Sales Qty
1	5
2	9

Desired result when we choose "Ton"

Item Number	Sales Ton
1	2.5
2	6.3

[Anonymous · Accepted solution]

Hi @DarkTracer888 

Thanks for the reply from Kedar_Pande .

@DarkTracer888 , Are you trying to merge the two measures you are using now? If so, please refer to the following test.

Here are data tables I used.

Create a measure as follows

Sales = 
VAR _SalesUnit = SUM('Sales'[Sales Qty])
VAR _SalesTon = SUMX('Sales',_SalesUnit * RELATED('Product Master'[Unit of Weight]))
return
Switch(True(),
SELECTEDVALUE(Selection[Selection]) = "Units", _SalesUnit,
SELECTEDVALUE(Selection[Selection]) = "Tonnes", _SalesTon,
_SalesTon
)

Output:

Best Regards,
Yulia Xu

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

[Kedar_Pande]

You already have a Selection table with two options: "Units" and "Tonnes." Ensure this table is linked to your model to control the dynamic measure.

Measure:

DynamicMeasure =
VAR SelectedCalculation = SELECTEDVALUE('Selection'[Value]) -- "Units" or "Tonnes"
RETURN
SWITCH(
TRUE(),
SelectedCalculation = "Units",
-- Handle Units for each fact table
SWITCH(
TRUE(),
ISINSCOPE('Sales'[Item Number]), SUM('Sales'[Sales Qty]),
ISINSCOPE('Stock on Hand'[Item Number]), SUM('Stock on Hand'[SOH Qty]),
ISINSCOPE('Current in Transit'[Item Number]), SUM('Current in Transit'[Transit Qty])
),
SelectedCalculation = "Tonnes",
-- Handle Tonnes for each fact table (Units * Unit of Weight)
SWITCH(
TRUE(),
ISINSCOPE('Sales'[Item Number]), SUMX('Sales', 'Sales'[Sales Qty] * RELATED('Product Master'[Unit of Weight])),
ISINSCOPE('Stock on Hand'[Item Number]), SUMX('Stock on Hand', 'Stock on Hand'[SOH Qty] * RELATED('Product Master'[Unit of Weight])),
ISINSCOPE('Current in Transit'[Item Number]), SUMX('Current in Transit', 'Current in Transit'[Transit Qty] * RELATED('Product Master'[Unit of Weight]))
)
)

With this measure, you can create a dynamic calculation that works across your Sales, Stock on Hand, and Current in Transit tables. You’ll only need this single dynamic measure, and the user can switch between "Units" and "Tonnes" via a slicer.

If this helped, a Kudos 👍or a Solution mark would be awesome! 🎉
Cheers,
Kedar Pande
Connect on LinkedIn