Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello friends,
I feel there should be a simple solution - please help.
I have an Actual table of expenses per Employee.
I also have a Budget table with budget per Department. (The Department is the common dimension)
When analyzing ActualVSBudget at the Department level - everything is fine.
But when I drill down to the Employee level (from Actual fact table) - then the Budget Numbers are REPEATED.
I would like to show no numbers (blanks) for Budget at the Employee level.
How to make my measure deal with this different granularity?
Attached is PowerBI .pbix sample.
https://1drv.ms/u/s!AoP_9ampPIT7jyo4wT-dWZe014-3
Thank you!
Michael
Solved! Go to Solution.
Hi,
Here's what i did. In the ActualVsBudget table, i removed Employee from the visual and dragged Employee from the Employees table back to the visual. I modified your Budgeted Amount field to
= if(HASONEFILTER(Employees[Employee]),BLANK(),SUM(Budget[BudgetAmt]))
Please have a look here
This may help
Regards
Tom
Hi,
Here's what i did. In the ActualVsBudget table, i removed Employee from the visual and dragged Employee from the Employees table back to the visual. I modified your Budgeted Amount field to
= if(HASONEFILTER(Employees[Employee]),BLANK(),SUM(Budget[BudgetAmt]))
Thank you guys,
The problem is that in the my real-world scenario - there are other fields in the Actual Fact table that do not have dimensions.
For example - Document_Number, that a user might drag to the pivot in order to analyze the Actual Amount.
I cannot define to check HASONEFILTER for every posssible field.
I actually need my Budget Measure to ALWAYS show blank if a user drills to anything OTHER THAN budget's granularity (Department, Date and ExpenseType - these are fixed).
Any ideas?
Thank you.
Michael
Please have a look here
This may help
Regards
Tom
Hi,
I dont think you have an option here. You will have to use the HASONEFILTER().
Hey @Anonymous,
the way @Ashish_Mathur proposed is the way to go. It's always a good idea to use the columns from the one oneside of the table relationship in your visuals, this means using the employee column from the employees table.
Using HASONEVALUE() will not work, due to the fact that there is one value for [ActualAmt], for this reason the repeated Budget value is shown whenever there is no value [ActualAmt] for all the other employees in the context of the department.
Instead use HASONEFILTER().
Here is a good article that explains some differences between both formulas:
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
Regards
Tom
Hey,
maybe you can use this approach.
You can check if there is a single value of the employee column contributing to the filter context, if this is the case show BLANK() else calculate your BUDGET measure like so:
the measure = IF(HASONEVALUE('tablename'[employee column]) ,BLANK() ,CALCULATE( ...) )
Hope this gets you started
Regards
Tom
Thanks Tom,
It does return blank only for the employee that has an Actual amout
The rest employees are shown with repeated values still....
Please help...
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |