Forum Discussion
Relative % calculation
Hi Justas4478 ,
i have developed a PBIX according to your ask in community, attaching Snips and PBIX. Please go through it and let us know the same you are expecting. happy to assist to you.
Regards,
Akhil.
Hi Justas4478
You can do this with a couple of measures. The key is that when you put StockStatus on the visual, Power BI filters it down to one status at a time, so a simple % calculation will always show 100%. To fix that you need the denominator to ignore the StockStatus filter.
Example:
Total Qty = SUM(Stock[Qty])In Stock Qty = CALCULATE([Total Qty], Stock[StockStatus] = "IN STOCK")Out of Stock Qty =
CALCULATE([Total Qty], Stock[StockStatus] = "OUT OF STOCK")% In Stock =
DIVIDE(
[In Stock Qty],
CALCULATE([Total Qty], REMOVEFILTERS(Stock[StockStatus]))
)
This gives you the relative % In Stock within whatever context is on rows (SKU, StockLevel, Plant etc.).
If you want the percentage to stay fixed at SKU + Plant level (instead of changing by StockLevel), you can use this version:
% In Stock (by SKU+Plant) =
DIVIDE(
[In Stock Qty],
CALCULATE(
[Total Qty],
REMOVEFILTERS(Stock[StockStatus]),
ALLEXCEPT(Stock, Stock[SKU], Stock[Plant])
)
)That way you can show both the row-level percentages and the locked percentages side by side. A 100% stacked column chart with In/Out Qty also works nicely for a visual split.
rohit1991 So it is a bit tricky since.
What I am using as my sum is rowcount.
Since each row can have stock in (DC01 and DC06 that are values on the same column) at the same time as well there can be where only one of them or neither have any stock. So same summarized row can only be counted as in stock if sum of stock values for DC01 & DC06 is more than 0, then that summed row is counted as in stock.
Thats why my sum measure is more complex than it would be normally.Hi Justas4478
In this case, wrapping it inside a SUMX or COUNTROWS with a condition works better:
In Stock Rows = COUNTROWS( FILTER( Stock, Stock[DC01] + Stock[DC06] > 0 ) )That way you’re checking each row first, then counting only those that qualify as “In Stock.” You can then divide that by the total row count to get the relative %.
