Forum Discussion

MCG's avatar
MCG
Icon for Helper I rankHelper I
6 years ago
Solved

Percent within Group

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

 

Need Help
  • kriscoupe's avatar
    kriscoupe
    6 years ago

    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

5 Replies

  • kriscoupe's avatar
    kriscoupe
    Icon for Solution Supplier rankSolution Supplier
    6 years ago

    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's avatar
      MCG
      Icon for Helper I rankHelper I
      6 years ago

      This is exactly what i was lookin for:)

      great thanks

      MCG

      • kriscoupe's avatar
        kriscoupe
        Icon for Solution Supplier rankSolution Supplier
        6 years ago

        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

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      MCG
      Icon for Helper I rankHelper I
      6 years ago

      Thanks

      I will also try your solution

      MCG