Calculated measure from 2 measures in different tables

[Anonymous]

Dear all,

Appreciate your advice for below issue with calculated measure in Power BI Desktop.

Context:

I created 2 calculated measures in 2 different tables. Then I want to create another calculated measure to calculate value between these measures.

Measure 1: Pattern (in data table Actual)

Measure 2: Projection (in data table Projection)

Measure 3: Difference = Patter - Projection

I already create modelling between these table of data.

Issue:

The result of measure 1 when put into 1 visual table with measure 2 is incorrect.

Please find the report in the attachment

https://drive.google.com/file/d/1jBDYT_4amHnZPhd6J_t_GyqojRwP8Qu3/view?usp=drivesdk 

Thanks for your support!

Measure 1:

Pattern =

Calculate(

Sum('Actual'[Order quantity]),

ALLEXCEPT('Actual','Actual'[SKU label],'Actual'[Order of week])

)

/

CALCULATE(

sum(Actual[Order quantity]),

ALLEXCEPT('Actual',Actual[SKU label])

)

Measure 2:

Projection =

Calculate(

Sum(Projection[Final CDP]),

ALLEXCEPT('Projection',Projection[Material],Projection[Order of week])

)

/

CALCULATE(

Sum(Projection[Final CDP]),

ALLEXCEPT('Projection','Projection'[Material])

)

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the formula.

Measure 3 = 
CALCULATE(
    SUM(Actual[Order quantity]),
    FILTER(
       Actual,
       Actual[SKU label]= MAX(Projection[SKU label]) &&
       Actual[Order of week] = MAX(Projection[Order of week])
    )
)
/
CALCULATE(
    SUM(Actual[Order quantity]),
    FILTER(
        Actual,
        Actual[SKU label] = MAX(Projection[SKU label])
    )
)

There's no relationship between two tables.

Best regards,
Lionel Chen

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

[Anonymous]

Hi Jimmy, could you please advise this issue? Thanks in advance

@Jimmy801 

[Jimmy801]

Hello @Anonymous 

i would need to see which fields you are placing in your visual. I cannot open your file, I don't have the latest version installed. 

Question... where your field "Material" is coming from? Actual or projection? If its projection then probably is not working.

How is your dataset build? How your tables are connected?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

The "Material" in the table above come from Projection. I created relationship between actual and projection but it's not working.

Data source comes from SAP BW.

Here I'd like to attach more details for your reference.

Could you please advice how can I solve the issue? Thanks a lot!

[Jimmy801]

Hello @Anonymous 

as long as I can't use the original dataset i can give you only a few advices, not the solution. You can try:

- Create a new table with a distinct value of your merge-column and connect both tables to this new table

- In your measures you are using in both the Order of week, but in one you are referencing the one form the actual-table in the other from the projection-table. In your visual you can only place or the one or the other. You could create 2 new tables with material and order of week, connect them to your tables and use these new dimension in your formulas and in your visuals. 

I have also to admit that I'm not an expert in DAX, but I know hot it works 🙂

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy