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