Percent within Group

[MCG]

Hi,

i have data set showing sales of product in category:

product  category  sales

a1             a            10

a2             a             20

b1             b            30

b2             b             40

how can i calculate measure that will return % of sales within category but i want to have only product list displayed on canvas:

product   share

a1            33% ( as result of 10 for a1 and category total a1+a2=30)

a2            66%

b1           43% (30/ 70)

b2           57%

thanks

mcg

[AntrikshSharma]

Just going one step further, you could try this as well:

% of Total =
VAR ProductSales =
    CALCULATE ( [TotalSales], ALLSELECTED ( Mcg[Product] ) )
VAR CategorySale =
    CALCULATE ( [TotalSales], ALLSELECTED ( Mcg[Category] ) )
VAR Result =
    IF (
        ISINSCOPE ( Mcg[Product] ),
        DIVIDE ( [TotalSales], ProductSales ),
        IF (
            ISINSCOPE ( Mcg[Category] ),
            DIVIDE ( [TotalSales], CategorySale ),
            DIVIDE ( [TotalSales], CALCULATE ( [TotalSales], ALLSELECTED ( Mcg ) ) )
        )
    )
RETURN
    Result

[MCG]

Thanks

I will also try your solution

MCG

[kriscoupe]

Hi @MCG,

You need to use the CALCULATE function to find your sales for all products in that category. This should work.

VAR ProductSales = SUM( Sales )

VAR CategorySales =

CALCULATE(

    SUM( Sales ),

    ALL( The table with products in ),

    VALUES( Your category column )

)

RETURN

ProductSales / CategorySales

Effectively the ALL/VALUES combination removes the filters on product but then puts them back on for category.

Let me know if this helps

Kris

[MCG]

This is exactly what i was lookin for:)

great thanks

MCG

[kriscoupe · Accepted solution]

Awesome @MCG  😀! Would you mind marking the answer as a solution so that other can find this in the future and close the thread as solved?

Thanks!

Kris