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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Show blanks instead of Repeated Values - Actual vs Budget

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!

MichaelActualVSBudget.JPG

 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Please have a look here

This may help

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi,

 

I dont think you have an option here.  You will have to use the HASONEFILTER().


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

ActualVSBudget2.JPG

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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