Calculate Percentage of Column Total

[hdsogno]

New to BI aand can't figure out how to do this.  There are 2 tables.  Table 1 has Product Group, Product Sub Group and Item.  Table 2 has Country, Years, Sales and Item.  The Matrix has two Slicers, 1 for Country and 1 for Product Group.  How do you calculate the percentage of Total Sales for each Product Sub Group for each Year?  I want the Matrix to look like below.  Any help would be appreciated.

Sales Matrix

[Syndicate_Admin]

I have managed to calculate the percentage for each month for each year (Divide). I would like your support to replicate the same but for the categories per year, I cannot make the sums by category (A, B) for each year and thus calculate their corresponding percentage per year.

I have managed to calculate the percentage for each month for each year (Divide). I would like your support to replicate the same thing but for the categories per year, I cannot make the sums per category (A,B) for each year and in this way calculate its corresponding percentage per year.

taje by month for each year, I want to replicate it by category for each year.

[v-xuding-msft · Accepted solution]

Hi @hdsogno ,

The formula that parry2k suggests works like a charm. And for your situation, you also could try using the function of ALLSELECTED like this:

Percentage_ALLSELECTED =
DIVIDE (
    SUM ( 'Table'[Sales] ),
    CALCULATE ( SUM ( 'Table'[Sales] ), ALLSELECTED ( 'Table' ) )
)

You could watch the video to learn about the difference between the two functions.

The Main Difference Between ALL And ALLSELECTED - Power BI & DAX 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hdsogno]

Hi Xue Ding -

You and parry2K made my day!  His solution gave me the percentage of all products.  I was was looking for a percentage of only the products selected so I changed the ALL in his solution to the ALLSELECTED that you suggested and that did the trick.

Thanks again!

[parry2k]

@hdsogno here it is

Measure = 
DIVIDE( 
SUM ( 'Table'[Amount] ), 
CALCULATE( SUM ( 'Table'[Amount] ), ALL('Table'[Product Sub Group]))
)

Would appreciate Kudos 🙂 if my solution helped. 

[hdsogno]

Thanks for your help.  Your solution works to get the percentage of ALL products.  I was looking for the percentage of only the products selected.  Substituted ALLSELECTED instead of ALL in your solution and it did the trick.  Thanks again.