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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Measure different grain

Hi everyone,

 

I have been working on Power BI for a few months and now reached a point where I really need help. So I hope you can and I already thank you for it.

 

Here is the situation :

 

My hierarchy is as follow :

 

Raw budget

ProjectPhaseBudget
A1x
A2x
A3x
A4x

 

Detailed budget

ProjectPhaseProductTaskTime Budget
A1auniqueIDx
A1auniqueIDx
A1buniqueIDx
A1buniqueIDx
A2auniqueIDx
A2auniqueIDx
B1vuniqueIDx
B1vuniqueIDx

 

Time tally table

ProjectPhaseProductTaskTime TallyDate
A1auniqueIDxddmmyyy
A1auniqueIDxddmmyyy
A1auniqueIDxddmmyyy
A1buniqueIDxddmmyyy

 

First I want to calculate the remaining time

I tried this :

 

Measure = VAR Timeleft = SUM ( DetailedBudget [Time] ) - SUM ( Timetally [time] ) 
RETURN
IF ( Timeleft > 0 ; Timeleft )

And its working when shown again the Task dimension but its wrong when I show it for a project (The total time spent is greater than the budget so this measure will show 0 time left when used with project dimension.

 

I kind of made it work with calculated columns but then the time left to do is not updating depending on the date slicer.

 

Then, I need to check for a given Phase, if there is no Detailed time budget then use the Raw Budget.

 

the result would be something like that:

 

ProjetPhaseRaw BudgetDetailed BudgetUsed BudgetTime spentTime left
A1101515114
A2201515213
A330 30327
A4403535431

 

 

Thank you very much for reading through this and please ask me if you need extra details. I'm so overwhelmed by this that just explaining it is difficult.

 

Cheers!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

1 REPLY 1
Anonymous
Not applicable

So I managed to solve it using SUMX and CALCULATE thanks to

 

https://community.powerbi.com/t5/Desktop/DAX-SUMX-REFERENCE-ANOTHER-TABLE/td-p/217040

 

Cheers.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.