Getting value as a measure

[D3K]

 Hi everyone!

Faced as a begginer with next problem, really hope for your help!

Need to get value of Profit Margin of parent group for any distinct Product as a measure, by clicking on a product name in a table on a Desktop sheet.

Here is the situation.

Will try to explain to be clear. I need to get Group Profit Margin for last 90 days (already calculated and made a support table) for any Product as a measure by clicking on product name, to use it in further calculations. The reason is: we already got a Cost Price for product and I want to make a next measure "Product Cost Price / (1 - it's Group Margin)" to get a Recommended Sale Price. 

Thank you!

[danextian · Accepted solution]

Hi @D3K,

You can try this DAX formula as a measure:

Group Margin =
VAR GroupMargin =
    SELECTEDVALUE ( ClassiferTable[Group ID], BLANK () )
RETURN
    CALCULATE ( [Group Margin Measure], 'Group Margin'[Group ID] = GroupMargin )

Replace the text strings above in red with the actual group margin measure.

Considerations;

• The measure has to be an aggregation. Power BI won't accept a column as an argument as it will return multiple values. By the looks of it, your Group Margin table already contains summarized values by Group ID. You can use MIN( 'Group Margin Table'[Group Margin] ),  MAX( 'Group Margin Table'[Group Margin] ),  SUM( 'Group Margin Table'[Group Margin] ) as long as it returns the correct group margin value.
• The formula will return blank if selecting two or more Product ID returns more than one Group ID. SELECTEDVALUE() returns a single value if, based on the current filter context, only a single value is being returned for Product ID. If not a single value is selected, it will return blank and there is no blank Product ID.
• It relies on the selection from Classifer table. If you selecting anyting from the Sales table and there's no relationship between Classifer and Sales table, then the measure won't work and will just return blank.

[D3K]

danextian, thanks a lot! It works! And definately what I need 

[danextian]

If that is so, please accep my post as solution to change the status to thread to resolved.