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
RT
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
Ashish_Mathur
Super User
Super User

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.


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

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

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


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

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...

Google Drive.


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

Hi,

 

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


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

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
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.