cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RT Frequent Visitor
Frequent Visitor

Binning a measure but performing a calculation based on the bin

Issue.JPG

 

Hello,

The goal is to create four measure for the green, yellow, red and gray boxes.  I am calculating the Days of Supply of items in a measure (dividing the total inventory from measures in multiple tables by the daily demand.  I now need to look at the days of supply for each item and bucket the total inventory into four groups, essentially good inventory, average, bad and terrible. 

 

As an example, the inventory on item DEF is $10,000 and it's days of supply is 45 so the 0-30 bucket gets 30 days multiplied by the daily rate of 222 to put $6,660 of the inventory into the Green/Good bucket and the extra 15 days of supply is applied to the 30-60 Yellow/Average bucket.  Then I sum it all up to show how much of the total inventory is Good, another measure to show what's Average, etc.

 

I can do it in Excel (see above) but I can't seem to figure out how to do it in Power BI with DAX since the Days of suppy is a measure and not a column in a table where I might be able to use a SUMMARIZE function to create a similar table and then do some similiar IF statements. 

 

Is there some CALCULATE/FILTER function on disconnected binning tables or something that might solve this...or some way to get the Item and the Days of supply measure into an internal model table that I'm missing?  Any help would be appreciated!  Thanks!

8 REPLIES 8
Super User
Super User

Re: Binning a measure but performing a calculation based on the bin

Hi,

 

Will you be OK if i can solve this using the PowerPivot and/or CUBE formulas in MS Excel?  If yes, then share the link from where i can download your Excel file.  In that Excel file, write the measures to compute the Inventory, Daily demand and Days of supply.

RT Frequent Visitor
Frequent Visitor

Re: Binning a measure but performing a calculation based on the bin

Unfortunately, I need to solve this in Power BI.  Is that still possible?

Super User
Super User

Re: Binning a measure but performing a calculation based on the bin

I am not sure.  If you can share a sample PBI file, i can try.

RT Frequent Visitor
Frequent Visitor

Re: Binning a measure but performing a calculation based on the bin

I do have a sample pbix, but I can't figure out how to upload it here...I only see photo's and video.  Sorry for being dense on how to submit it...

Super User
Super User

Re: Binning a measure but performing a calculation based on the bin

Google Drive.

RT Frequent Visitor
Frequent Visitor

Re: Binning a measure but performing a calculation based on the bin

Super User
Super User

Re: Binning a measure but performing a calculation based on the bin

Hi,

 

I read a message saying that I need permission to download the file.

RT Frequent Visitor
Frequent Visitor

Re: Binning a measure but performing a calculation based on the bin

Thanks, but I've sort of solved it by using an Add columns and summarize statement to create a new table with the measures and then did the IF statements to get the columns.  Only downside was I then couldn't create relationships back to the attributes table that created it because it gave me a circular dependency. 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 404 members 3,432 guests
Please welcome our newest community members: