Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
I'm sorry to ask as I'm sure this is a standard query, but have been searching and trying for some time and can't quite get the answer to what I am trying to do. Please see some simplified data below:
Work Order | Job Number | Phase | W/O Total | |||
73657-1 | 1 | Forecast | 14360.39 | |||
74975-1 | 1 | Confirmed | 14360.39 | |||
74719-1 | 2 | Billed | 1100 | |||
75110-1 | 2 | Forecast | 5000 | |||
74660-1 | 3 | Forecast | 40000 | |||
74695-1 | 4 | Confirmed | 210402.15 |
What I am trying to do is create some DAX measures that will eventually give me a report that tells me the following.
A) When a Job has more than a 10% difference between Forecast and Confirmed/Billed
B) If there is a Job that has a Confirmed/Billed status but no Forecast
C) If there is a Job that has a Forecast status but no Confirmed/Billed
I have managed to get fairly close but in very inefficient ways with multiple calculated columns and I feel that there must be a better way to pull it together, as you can probably tell I am fairly new to PowerBI, but have managed to create some great reports and learning more every day so any advice to get me closer would be greatly appreciated.
The only slightly more complex thing is that I have another table that does the job lookup using the Job Number field as the relation, but I'm fairly confident that I can tie the data together if someone could point me in the right direction for what I've posted above.
Thanks to anyone for looking,
Dave
Hi @davidhh90 try to create calculated column with IF applying your logic, the same as in Excel. After that calculation or amount (creation of measure) should be very easy (based on your snapshop). Hope this help.
Proud to be a Super User!
Hi @some_bih thanks for the reply. I agree it should be a simple if formula and i've tried to do this in a calculated column. But I'm not sure how to make it lookup using the job field against those multiple variables at once. Currently, I can only see a way of doing it making multiple calculated columns for my 3 questions. Could you suggest the formula to use?
Hi @davidhh90 I am not familiar with your model to answer properly. I understand, C is like B only operaror is different.
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
18 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
25 | |
10 | |
10 | |
9 | |
6 |