How to display data beyond the Granularity of the Model


#1

Hi, I am wondering if you could point me to anything that you’ve done that will give some direction on how to handle when we drop an attribute in a table (or whatever else) that is beyond the granularity of the data model.
I am thinking that I potentially would want to create a Percentage that corresponds to the amount that it contributed to the intended level of granularity. If this doesn’t totally make sense, I will try to re-phrase my question.

Thanks


#2

Hi Mark, can you post a specific example here of what you mean.

With percentages in Power BI, it’s all about using the ALL function correctly.

Here’s where to find information on that - http://portal.enterprisedna.co/courses/108878/lectures/2217095


#4

Hi Sam, thanks, that helped a lot.
Now I am having an issue with all of the items from the item dimension being displayed if the TTL$ column is empty and the budget is not.
I have attached a very ugly screen capture to get an idea
The row that starts with 1022, if I expand that, all of the items from the item dimension get returned.
In the third row, 1140, when I expand that, only the items associated with the $110,000 get returned.
The item that gets returned, is beyond the grain of the budget.
Ultimately, I am looking to not return any items where the value for total is blank.

ThanksItemReturns


#5

The only reason they are showing up is because there seems to be a value on the budget calculation. So if you don’t want the blank to show up you will need to add some logic around the budget measures that returns a BLANK for that result as well.

Some simple IF logic should be able to do this.

IF( ISBLANK( TTL ), BLANK(), TTL ) …something like this.


#6

Thanks for the response. To clarify, I do want a budget value in the budget column if one exists even if there aren’t any associated total dollars.

The issue I am having is that when I expand the + and there are no total dollars, every item from the items table is displayed. If there is a value for total dollars, only the items from the items table that correspond show up. This is the desired outcome.
When I expand the Row that has no total dollars and a budget amount, I’d like nothing from the items table to be returned.
I’m pretty sure the solution you suggested would hide the budgeted amount in cases where there wasn’t a total dollar amount. Thanks


#7

I think I’m 90% of the way there understanding this. To me it’s just a matter of adding BLANK() logic into the calculation somewhere to make sure you don’t get results there.

It may be somewhere slightly different to what I’m saying but somewhere in your formula this will get rid of those items.