Percentage Contribution to all Levels using measures

[Anonymous]

Hi Team,

Need help here to calcuate Percentage Contribution to all the levels  .

1.Product to Total Contribution is working fine for me.

ProductPerc =

SUM (PRODUCTS[SALESAMOUNT])

/

CALCULATE (

SUM (PRODUCTS[SALESAMOUNT]),

ALL (Products[ProductName])

)

2.Prod to SubCat Contribution  is not working .

3.Prod to Category Contribution is not working.

Attached the Excel As well which has both data and output(Formula)

https://drive.google.com/open?id=1d2T6ktxhJBn9zwveVfif9ys8zqnTuwRj

My Data:

Category	Subcategory	ProductName	SalesAmount
Market1	Individaul Person	Electronic	7930.75
Market1	Individaul Person	Fashions	9943.13
Market1	Individaul Person	Motors	2102.55
Market1	Legal Person	Electronic1	1449.65
Market1	Legal Person	Fashions2	1364.68
Market1	Legal Person	Motors3	4253.76
Market2	Individaul Person	Electronic4	3611.83
Market2	Individaul Person	Fashions5	5028.03
Market2	Individaul Person	Motors6	4145.26
Market2	Legal Person	Electronic7	5891.17
Market2	Legal Person	Fashions8	4964.96
Market2	Legal Person	Motors9	5766.34

Output:

[Anonymous · Accepted solution]

I'll attach the pbix below, but here's the idea:

• Created dimenson tables for Category, Product and SubCategory and related those to your main table (not really needed in this case, but you would be better off storing keys in your main - aka fact - tables).  You can see how I did that in Query Editior
• With Product Name from the new dimension product table:
• Prod to total is the easiest:

Total Sales = sum( Table1[SalesAmount] )

Grand Total Sales Product = CALCULATE( [Total Sales], all( DimProductName[ProductName]))

Product to Total Contribution = DIVIDE( [Total Sales], [Grand Total Sales Product] )

• But now we get a little more tricky since category and subcategory arent in the table:

• Grand Total of SubSales = 
  CALCULATE( 
      SUMX( 
          ADDCOLUMNS(
              SUMMARIZE( Table1, DimProductName[ProductName], DimSubcategory[SubCategory]),
              "Total Sub Sales", CALCULATE( [Total Sales], FILTER( ALL( Table1), Table1[Subcategory] = EARLIER( DimSubcategory[SubCategory])
  )
      )
          ), 
          [Total Sub Sales]
      )
  )
  
  Product to SubCat Contribution = 
  DIVIDE(
      [Total Sales],
      [Grand Total of SubSales])
      

  then the same logic for category:

• Grand Total of Category = 
   
  CALCULATE( 
      SUMX( 
          ADDCOLUMNS(
              SUMMARIZE( Table1, DimProductName[ProductName], DimCategory[Category]),
              "Total Sub Sales", CALCULATE( [Total Sales], FILTER( ALL( Table1), Table1[Category] = EARLIER( DimCategory[Category])
  )
      )
          ), 
          [Total Sub Sales]
      ))
  
  Produc to Categoy Contr = DIVIDE( [Total Sales], [Grand Total of Category])

  Final matrix. Dont need or probably want the grand total on each row, but left them on there for demonstration purposes
• 

here's the file:

https://1drv.ms/u/s!Amqd8ArUSwDS1UheqO9YNXskj5R1?e=VniWYv

[HKSpring]

Hi,

May I request the pbix file again?  The above attached link is expired or not working anymore.

Tks in advance