Relative % calculation

[Justas4478]

Hi, I have this multi measure solution that calculates % of In Stock and Out of Stock.

What I am trying to do is as well to incorporate Stock Levels, since if I add them to the visuals it changes % to 100%.
This is how data looks at the moment.

As and example I want to show stock level % of in stock for G3P0. As you can see instock for G3P0 is 61.2%
When I try to show it ot then treats instock as a 100% and gives these numbers.

Were I would want to get these results instead.
On quick calculations these are percentages that I would expect more or less.

76.5% of 61.2%

46.88%

19.13% of 61.2%

11.71%

4.37% of 61.2%

2.67%

I am not sure how to approach this problem.
G3P0 is only one example I chose but solution should not limit only to that particular one.
-----------------------

Stock Status sum =

VAR BinGroups =

    ADDCOLUMNS(

        SUMMARIZE(

            'fix bin & Stock',

            'fix bin & Stock'[Product (fix)],

            'fix bin & Stock'[Storage Type],

            'fix bin & Stock'[Storage Bin]

        ),

        "TotalStock",

            CALCULATE(

                SUM('fix bin & Stock'[Unrestricted Stock]),

                'fix bin & Stock'[Plant] IN {"DC01", "DC06"}

            ),

        "StockStatus",

            IF(

                CALCULATE(

                    SUM('fix bin & Stock'[Unrestricted Stock]),

                    'fix bin & Stock'[Plant] IN {"DC01", "DC06"}

                ) = 0,

                "OUT OF STOCK",

                "IN STOCK"

            ) )

RETURN

    COUNTROWS(BinGroups)

----------------------------

Stock Status count(IN STOCK) =

VAR StatusToCount = "IN STOCK"  -- Change to "OUT OF STOCK" for the other measure

VAR BinGroups =

    ADDCOLUMNS(

        SUMMARIZE(

            'fix bin & Stock',

            'fix bin & Stock'[Product (fix)],

            'fix bin & Stock'[Storage Type],

            'fix bin & Stock'[Storage Bin]

        ),

        "TotalStock",

            CALCULATE(

                SUM('fix bin & Stock'[Unrestricted Stock]),

                'fix bin & Stock'[Plant] IN {"DC01", "DC06"}

            ),

        "StockStatus",

            IF(

                CALCULATE(

                    SUM('fix bin & Stock'[Unrestricted Stock]),

                    'fix bin & Stock'[Plant] IN {"DC01", "DC06"}

                ) = 0,

                "OUT OF STOCK",

                "IN STOCK"

            ))

RETURN

    COUNTROWS(

        FILTER(

            BinGroups,

            [StockStatus] = StatusToCount

        ))

------------------------

Stock Status %(IN STOCK) = DIVIDE('fix bin & Stock'[Stock Status count(IN STOCK)],'fix bin & Stock'[Stock Status sum])

[v-agajavelly]

Hi @Justas4478 ,

Just checking in did you get a chance to look into the PBIX file?

Regards,
Akhil.

[v-agajavelly · Accepted solution]

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.

[Justas4478]

@v-agajavelly Hi, they look like what I am trying to get, I will check pbix file asap

[rohit1991]

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.

[Justas4478]

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

[rohit1991]

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