aat%Share Category share and sub-category and customer share

[DeepDive]

Hi All, I have category wise, sub category wise and customer wise data. Now I want to compare % share at levels.

My requirement is when it's category it compare the total but when it is subcategory or customer it compares to it's parent category only like below :

Link for sample excel file :

https://www.dropbox.com/scl/fi/d1hw2hlacsa7w0xs9bkfo/SalesPI.xlsx?dl=0&rlkey=gxqgwn6e790s0u4vipl3kr7...

[v-kelly-msft · Accepted solution]

Hi @DeepDive ,

I used part your data to create a sample .pbix file,see below:

Create a measure as below:

Share% = 
var _sharetotal=DIVIDE(CALCULATE(SUM('Sales'[Qty]),FILTER(ALL('Sales'),'Sales'[Category]=MAX('Sales'[Category]))),CALCULATE(SUM('Sales'[Qty]),ALL('Sales')))
var _sharesubtotal=DIVIDE(CALCULATE(SUM('Sales'[Qty])),CALCULATE(SUM('Sales'[Qty]),FILTER(ALL('Sales'),'Sales'[Category]=MAX('Sales'[Category]))))
Return
IF(ISINSCOPE('Sales'[Sub Category]),_sharesubtotal,IF(ISINSCOPE('Sales'[Category]),_sharetotal,BLANK()))

And you will see:

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@DeepDive , Try like

divide(calculate(Sum(Table[Total])),calculate(count(Table[Total]), allexcept(Table[Category])))

[DeepDive]

this dax not giving me correct figures.

[DeepDive]

Hi @amitchandak , I think there is some typo error in your dax ("Count"),

am using this below :

Measure = divide([Total],calculate([Total], allexcept('Product','Product'[Category])))

But at category, level it's giving me 100% , i want to compare category with total of category.

[amitchandak]

@DeepDive , Try like

Measure = divide([Total],calculate([Total], allexcept('Product','Product'[Category],'Product'[Sub Category]), allexcept('Customer','Customer'[Customer])))