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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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