Blanks instead of Repeated Values - Actual vs Budget different granularity

[Anonymous]

Hello friends,

I've previously posted this question but didn't find a general solution yet.

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.

More important - how can I build the measure so that it will show blanks for ANY FIELD of Actual Fact?

How to make my measure deal with this different granularity?

Attached is PowerBI .pbix sample.

https://1drv.ms/u/s!AoP_9ampPIT7jyo4wT-dWZe014-3

There are solutions that handle it field-by-field, such as this:

https://www.daxpatterns.com/handling-different-granularities/

http://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/

But I need a general solution for ALL fields.

Thank you!

Michael

[rks87]

Hi Michael,

if this reaches you I would like to ask whether you found a generic solution on your problem and if yes, whether you would want to share it. Thank you 🙂

Best regards,

Konstantin

[Anonymous]

@rks87 

Hi Konstantin,

Unfortunately I haven't discovered a general solution yet.

So for now I use 

= if(HASONEFILTER(Employees[Employee]),BLANK(),SUM(Budget[BudgetAmt]))

and add every column that I think a user might drag into the report.

I do still believe however that there IS a general solution (as there is in the MDX), I just quit looking for it for now...

I do belive that DAX is powerful enough to solve this elegantly.

If you figure this out - please let me know too.

Thanks and good luck!

Michael

[rks87]

Hi Michael,

if this reaches you I would like to ask whether you found a generic solution on your problem and if yes, whether you would want to share it. Thank you 🙂

Best regards,

Konstantin

[PaulDBrown]

@Anonymous

Try this (adjust to your own columns/measures):

Blank budget for employees = IF(HASONEFILTER(employee[employee]), BLANK(), [Budget Amount])

(substitute the "employee[employee]" part with the column you are using for employees in the matrix visual)

Use this new measure instead of the [Budget Mount] you were using in the matrix visual.

Hope that works,

Regards,

Paul.

[Anonymous]

Thanks Paul,

But it is exactly the solution that I've mentioned in my post, and it is not general.

It references specific column and I need the solution for ANY column of my Actual Fact table.

Thanks!

Michael

[Ashish_Mathur]

Hi,

There will not be a generic solution to this one.  You will have to write every measure with a IF(HASONEVALUE().

[Anonymous]

Thanks @Ashish_Mathur 

I understand that I have to build every measure with some sort of solution.

That's not the problem.

My challenge is to build the solution that will work for EVERY ATTRIBUTE COLUMN from the Actual Fact table that a user will use in the report.

So I would like my solution to somehow make my Budget measure blank - for EVERY column of my Actual table without specificaly stating each column.

There HAS TO BE a solution. I believe DAX is powerful enough...

Please help

Thank you

Michael