How do I manipulate measure values based on 2 other dimension tables

2
votes

Power BI newbie here and I'm trying to figure how to craft my DAX to manipulate my measure values based on certain criteria in the other two tables.

Currently I have 2 separate tables which are joined by a One to Many relationship and a separate Measures table. (Total Sales Price is computed as sum of Sales Price)

My aim is to create a new measure where Total Sales Price is multiplied by 1.5x when DIM_Product_Type[Product Category] = "High".

New Measure = 
    CALCULATE (
        SUM ( FACT_PriceDetails[Sales Price] ),
        FILTER ( DIM_Product_Type, DIM_Product_Type[Product Category] = "High" )
    ) * 1.5

However this returns no values in my visual and I'm trying to discern if its a matter of the table joins or the DAX expressions.

Thank you for your time!

enter image description here

2
powerbipowerbi-desktop
Are your 'Product Serial Numbers' the same data type? If so do you actually have values for Category = "High"? – StelioK

2 Answers

1
votes

Your measure seems good.

It will select only those products with a Product Category of "High" and multiply them by 1.5 to give you result. i.e. Give me the sum of all "High" Product category Price details multiplied by 1.5.

What you need to check is:

  1. Product Serial Numbers match across the two tables
  2. Your Product Category does indeed contain the category "High"
  3. You have entries in FACT_PriceDetails that link to a DIM_Product_Type that has a category of "High"
  4. Check you have not set any filters that could be hijacking your results (e.g. excluding the "High" product category product type or the realated fact/s)
0
votes

Option-1

You can do some Transformation in Power Query Editor to create a new column new sales price with applying conditions as stated below-

First, Merge you Dim and Fact table and bring the Product Category value to your Fact table as below-

enter image description here

You have Product Category value in each row after expanding the Table after merge. Now create a custom column as shown below-

enter image description here

Finally, you can go to your report and create your Total Sales measure using the new column new sales price

Option-2

You can also archive the same using DAX as stated below-

First, create a Custom Column as below-

sales amount new = 
if(
    RELATED(dim_product_type[product category]) = "High",
    fact_pricedetails[sales price] * 1.5,
    fact_pricedetails[sales price]
)

Now create your Total Sales Amount measure as below-

total_sales_amount = SUM(fact_pricedetails[sales amount new])

For both above case, you will get the same output.