Visual Matrix - Actual vs Planned by workweek

[97906198]

Hi,

Need help on the following. I'm trying to plot a table where the jobs number calculated automatically in visual matrix. Tried few times but doesn't work.

Thanks alot.

[Ritaf1983 · Accepted solution]

Hi @97906198 
You should complete a few steps to get the desired result:

1. With PQ, you should unpivot the data table :

2. To have a status on the shorter format I added fixed status with column from examples ( this step isn't a must) 

3. close and apply a PQ and create these 3 measures:

Planned = CALCULATE(DISTINCTCOUNT('Table'[Job]),'Table'[Status fixed]="planned")+0

Actual = CALCULATE(DISTINCTCOUNT('Table'[Job]),'Table'[Status fixed]="actual")+0

Backlog = if([Planned]<[Actual],0,[Planned]-[Actual])

4. Put the weeks as columns and measures as values on the matrix

5. From format options switch the values to rows

The file with an example is attached to the response, so you can follow all the steps.

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

[97906198]

Thank you so much. There is one issue with your proposed solution. My Job# is in relationships with other tables. By unpivoting the table, it creates multiple lines with the same ID# which is not allowed in this case (which i have to delete the relationship). Any quick fix on this problem ?

[Ritaf1983]

Hi @97906198 

This happens because you use the fact table to filter others, which is not efficient practice.

If the job# is your primary key, then create another table with unique jobs and use it to create relationships.
For more information please refer :

https://www.seerinteractive.com/insights/join-many-many-power-bi

(this is a more detailed explanation of bridge tables)

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

this is about modeling

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

[97906198]

Thanks. let me try it out.

one more question on the formula on Backlog.

it should go like this - Current week backlog = previous week backlog + current week Planned - current Actual. Am I able to update to this ?

[Ritaf1983]

Hi @97906198 
Yep, the logic of the measure doesn't matter.
I wrote it just according to your example, you can modify it as you need.

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