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

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

Reply
aerizk
Advocate I
Advocate I

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

DateProductCountryMeasureValue
Jan-24AUSQuantity10
Jan-24BUSQuantity5
Jan-24CUSQuantity25
Jan-24AUSAmount100
Jan-24BUSAmount55
Jan-24CUSAmount125
Jan-24AUSUOM500
Jan-24BUSUOM250
Jan-24CUSUOM1000

 

Table with factors:

ProductFactor
A5
B50
C10
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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
  

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
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
  

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.