Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I have a report calculating how many hours an employee works. It has a formula that like:
SUM(Regular Hours) + SUM(Extra Hours) - Lunch Deduction.
I need to find out from the user what the lunch deduction is, so I"ve created a parameter with values .50, 1.0, 1.5, etc. But now I don't know how to use this parameter on the report. How do I display it, and how do I grab the value and add to my formula?
Thanks.
Solved! Go to Solution.
@Anonymous
With this code:
WTF =
SUMX(WTF, [Working Hours Total] - 'Lunch Hours'[Lunch Hours Value])
See here:
Half hour lunch
One hour lunch
Works for me. Maybe you could share sample data and your pbix file.
F
Hi @Anonymous ,
When you create a parameter you'll find it in the Fields pane at the right with a table of one column with a series of values depending on your choice when you created it, and a measure:
You just have to use that measure in your calculations.
Regards,
Fernando
Thank you Fernando, I must have not created the parameter properly because it is not in my field pane. From the Power Query Editor Window, I clicked Manage Parameters, then New, and created Lunch Deduction Amount, then hit Close and Apply. Is there something I am supposed to do after this?
In your case you need to use a What If Parameter, which is located under the Modeling ribbon:
Cheers,
Fernando
Thank you Fernando,
That worked but I can't get it to work in a formula, it just keeps using the alternateresult of 0.50, even though the slider says a different value:
WTF = 'QGenda Compliance'[Worked Hours]-'Lunch Deduction Amount'[Lunch Deduction Amount Value]
@Anonymous
With this code:
WTF =
SUMX(WTF, [Working Hours Total] - 'Lunch Hours'[Lunch Hours Value])
See here:
Half hour lunch
One hour lunch
Works for me. Maybe you could share sample data and your pbix file.
F
I got it working. What I did different...My Worked Hours was a Column, so I created it was a Measure (never know when to use column or measure), then I used the SUMX like you did and it worked. Thank you so much for your patience and help!
Good to hear. Rule of thumb: Always use measures, never use calculated columns. Unless it's completely necessary.
😉
F
Thank you, I am going to go back and change my calculated columns into measures.
Maybe I should open a new thread on this, but can you use IF statements in Measures? This is my formula in a column now, and trying to put in a measure, but it's not allowing me to pick that first field name (tagname):
Extra Pay Hours = IF('QGenda Compliance'[TagName] IN {"Shift: Specialty Shift", "Shift: Additional Shift", "Call: Specialty (Beeper)", "Call: Beeper", "PTO Buy-Back"}, 'QGenda Compliance'[Worked Hours], 0)
@Anonymous
You should not use calculated columns unless you don't have ANY OTHER CHOICE. However, turning calculated columns into measures is not always a good decision. Please note that a calculated column can be used in slicers, whereas measures can't. If you need a column, then the best solution by far is to calculate it in Power Query.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |