Best way to display a measure calculation

[aerizk]

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  * RELATED( ConversionFactorTable[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

[Greg_Deckler · Accepted solution]

@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
  

[aerizk]

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