category contribution with mutliple categories in the list of items.

[Anonymous]

Dear community,
Can you please assist me in writing a dax formula similar to sumif, also, show the result in the same matrix where a total contribution is viewed. 
please view my attachment to get a better idea. 

what I need to show is (if a product from the spice category is in scope then sales of that product divided by sales from total spice category in current filter context))

Thank you so much in advance. 

[v-xuding-msft]

Hi @Anonymous ,

Please try this:

Calculated column:

Column = 
var cat = CALCULATE(SUM('Table'[sales]),ALLEXCEPT('Table','Table'[cat]))
return
DIVIDE('Table'[sales],cat)

Measure:

Measure = 
var cat = CALCULATE(SUM('Table'[sales]),ALLEXCEPT('Table','Table'[cat]))
return
DIVIDE(SUM('Table'[sales]),cat)

[Anonymous]

Dear v-xuding-msft, 
Thank you for the reply, 
however, my problem is not yet solved. 

Firstly, I am sorry for not elaborating on my data model. Unlike the example given above, 
in my model, the "factsale" table doesn't have the category name. 

There is another table for categories and the "factsales" table is connected to "dimcategory " via "dimproducts".

when I Run your solution above, the result is 100% every where 
connection on my data model

[amitchandak]

@Anonymous , Think this requires a dynamic segmentation.

Refer to this file attached after signature and video on a similar topic. The file also have a cumulative  bucket video does not explain that.

https://youtu.be/CuczXPj0N-k