Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
demand -> demand for that week per product
Inventory Value is inventory for that product for that week.
Key-> is the unique identifier for a product.
Has 2 weeks of supply -> if w1 inventory value of a product is greater than or equal to (this week’s demand for that product + next week’s demand for that product, then yes otherwise no).
% of product that has 2 weeks of supply -> number of yes in has 2 weeks of supply / total number of products , per week.
Final result is % of products that has 2weeks of supply column in 2nd table.
Key | Date | Demand | Inventory Value | Has 2 weeks of supply? |
A1 | Monday, July 29, 2019 | 10 | 50 | If(50>10+20,1,0) ->1 |
A2 | Monday, July 29, 2019 | 20 | 20 | If(20>20+20,1,0) -> 0 |
A3 | Monday, July 29, 2019 | 5 | 20 | If(20>5+10,1,0) ->1 |
A1 | Monday, August 5, 2019 | 20 | 30 | If(30>20+30,1,0) ->0 |
A2 | Monday, August 5, 2019 | 20 | 40 | If(40>20+5,1,0) ->1 |
A3 | Monday, August 5, 2019 | 10 | 5 | If(5>10+10,1,0) ->0 |
A1 | Monday, August 12, 2019 | 30 | 30 | If(30>30+10,1,0) ->0 |
A2 | Monday, August 12, 2019 | 5 | 40 | If(40>5+10,1,0) ->1 |
A3 | Monday, August 12, 2019 | 10 | 50 | If(50>10+60,1,0) ->0 |
A1 | Monday, August 19, 2019 | 10 | 50 | If(50>10+30,1,0) ->1 |
A2 | Monday, August 19, 2019 | 10 | 30 | If(30>10+40,1,0) ->0 |
A3 | Monday, August 19, 2019 | 60 | 50 | If(50>60+10,1,0) ->0 |
A1 | Monday, August 26, 2019 | 30 | 40 | |
A2 | Monday, August 26, 2019 | 40 | 30 | |
A3 | Monday, August 26, 2019 | 10 | 10 | |
This should be our final table to be used in a visual:
Date | % of products that has 2weeks of supply |
29th July | (1+0+1)/3 |
5th August | (0+1+0)/3 |
12th August | (0+1+0)/3 |
19th August | (1+0+0)/3 |
Solved! Go to Solution.
Ah, I just realized my mistake. I didn't see that you had to sum this week's demand with next week's demand in order to get your boolean value. The measure gets a bit crazier since we need to determine the [Demand] from the next week for each value of Key:
%Products with 2wk Supply = VAR nextWeek = SELECTEDVALUE('Table'[Date])+7 RETURN IF(nextWeek IN ALL('Table'[Date]), //Handle the case with no Demand entries for next week DIVIDE( SUMX('Table', IF(('Table'[Demand] + CALCULATE(SELECTEDVALUE('Table'[Demand]), FILTER(ALLEXCEPT('Table', 'Table'[Key]), 'Table'[Date] = nextWeek)))
<'Table'[Inventory], 1) ), COUNT('Table'[Key]) )+0)
If all you're after is your second table, and you don't care about storing the intermediate results, you should be able to use this measure in a visual that splits data by date:
% of products with 2week supply = DIVIDE( CALCULATE(COUNTROWS('Table1'), FILTER('Table1', 'Table1'[Demand]<='Table1'[Inventory Value]/2)),
COUNTROWS('Table1'))
You can also use a similar COUNT measure with the same filter, but instead of dividing it by the total, in the values bucket, choose to show it as a % of grand total or row/column total if you're using a matrix:
@Cmcmahan : Thanks for quick reply. However it is not giving me correct answers in all cases.
Key | Date | Demand | Inventory Value |
A1 | Monday, July 29, 2019 | 10 | 100 |
A2 | Monday, July 29, 2019 | 20 | 100 |
A3 | Monday, July 29, 2019 | 5 | 1 |
A1 | Monday, August 5, 2019 | 20 | 40 |
A2 | Monday, August 5, 2019 | 20 | 30 |
A3 | Monday, August 5, 2019 | 10 | 20 |
A1 | Monday, August 12, 2019 | 30 | 30 |
A2 | Monday, August 12, 2019 | 5 | 50 |
A3 | Monday, August 12, 2019 | 10 | 50 |
A1 | Monday, August 19, 2019 | 10 | 70 |
A2 | Monday, August 19, 2019 | 10 | 20 |
A3 | Monday, August 19, 2019 | 60 | 100 |
A1 | Monday, August 26, 2019 | 30 | 20 |
A2 | Monday, August 26, 2019 | 40 | 60 |
A3 | Monday, August 26, 2019 | 10 | 2 |
It gives following results:
July 29th - 0.67
August 5th - 0.67
August 12th - 0.67
August 19th - 0.67
this is because, inventory value is not divided equally among two weeks.
How are those not the correct results?
For Jul29, A1 & A2 have enough inventory for 2+ weeks.
For Aug5, A1 & A3
For Aug12, A2 & A3
For Aug19, A1 & A2.
Each of these is 2/3 or .67 or 66.7%. If you don't like the format of the output, you can change it in the modeling ribbon.
August 5th - only A2. A1's Inventory = 40, demand for 5th Aagust =20, demand for 12th august = 30, so 30+20>40, hence it doesn't have two weeks of supply.
Ah, nevermind. You've posted a table with different supply values in your reply than what was in your original post. The 66% result is correct for most values in the 2nd version.
Which version of the data is the measure being used on? Could you put it all into a screenshot?
Hi,
This is dummy data. My dataset is huge, that's why I expained the logic it step by step.
Ah, I just realized my mistake. I didn't see that you had to sum this week's demand with next week's demand in order to get your boolean value. The measure gets a bit crazier since we need to determine the [Demand] from the next week for each value of Key:
%Products with 2wk Supply = VAR nextWeek = SELECTEDVALUE('Table'[Date])+7 RETURN IF(nextWeek IN ALL('Table'[Date]), //Handle the case with no Demand entries for next week DIVIDE( SUMX('Table', IF(('Table'[Demand] + CALCULATE(SELECTEDVALUE('Table'[Demand]), FILTER(ALLEXCEPT('Table', 'Table'[Key]), 'Table'[Date] = nextWeek)))
<'Table'[Inventory], 1) ), COUNT('Table'[Key]) )+0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |