Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to create a % from a single column value

Hi All,

 

I kindly need your help to solve my problem. Currently I have data like below table:

 

and i would like to create a report such as below template:

 

 

The problem that I have is related to contribution %. Actually it is a simple calculation from total gross sales. For "SKU Contribution", the calculation is gross sales per SKU/gross sales product category which means the when the % per SKU summed up, the total % for every product category is 100%. Meanwhile for Channel Contribution calculation is gross sales contribution by channel by sku or by product category/total gross sales.

 

Can  anyone help me explaining how to create such report template in power BI? If i should use custom column, kindly explain how to create the code.

 

I really appreciate all of your comments.

 

Thank you

 

1 Reply

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    Create 2 measures

    Gross Sales Amount = SUM(Table1[Gross Sales]) 

Contribution by SKU = [Gross Sales Amount] / CALCULATE(SUM(Table1[Gross Sales]), ALL(Table1[Type]))

    Pull 'Gross Sales Amount' into the Values well of a matrix.

    Pull 'Contribution by SKU' twice into the Values well of the matrix. For the second one, right-click and choose 'Show value as '->Percent of Row Total.   Then rename the second one 'Contribution by Channel'