AllExcept column with slicer value

Question

Hi,&nbsp;I am trying to create Allexcept for profit at column level. At one customer level it works fine.But i was trying to slice down to category level by selecting one value at category the AllExcept should be calculated at category level it is not giving me the right result.&nbsp; Can anyone please help in this.&nbsp;Dax which i was trying:-&nbsp;Profit by customer = var A = CALCULATE(SUM(Orders[Profit]),ALLEXCEPT(Orders,Orders[Customer Name]))var B = CALCULATE(SUM(Orders[Profit]),ALLEXCEPT(Orders,Orders[Category]))var c = IF(ISFILTERED(Orders[Category]),B,A)return cNote: I need AllExcept at column is because i need to create buckets based on this results.Power BI File: -https://we.tl/t-VttSGqFMpX&nbsp;Thanks,Akhil.&nbsp;

amitchandak · Answer

Akhil_1411 , I think this should help to change the calculation
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

akhil_1411 · Answer

Not getting the right result&nbsp;amitchandak&nbsp;

anonymous · Answer

Hi&nbsp;Akhil_1411&nbsp;,&nbsp;You are creating too many columns which may not be the right approach.&nbsp;Use the filter context of Power BI Engine.i have created most of your queries via measures.&nbsp;See image&nbsp;&nbsp;&nbsp;&nbsp;Total Sales = Sum(Orders[Sales])

Total Profit = SUM(Orders[Profit])

Measure Profit Ratio = DIVIDE([Total Profit],[Total Sales])

Measure Profit Buckets = 
SWITCH(TRUE(),
[Measure Profit Ratio]&gt;0.0 &amp;&amp; [Measure Profit Ratio]&lt;=0.05,"0%-5%",
[Measure Profit Ratio] &lt;=0.1,"0%-10%",
[Measure Profit Ratio] &lt;=0.2,"10%-20%",
[Measure Profit Ratio]&lt;=0.5,"20%-50%"
)&nbsp;Regards,Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)Did I answer your question? Mark my post as a solution!

akhil_1411 · Answer

Anonymous&nbsp;,&nbsp;The reason why iam creating the column is to fix at the cuatomer level profit or sales and then calculate the bucket size.&nbsp;And this bucket should dynamically change when i select any slicer value in category.This is my requirement&nbsp;In your case if i create the buckets like that lets say if i select one customer he falls into multiple buckets which is incorrect.&nbsp;Hope you understood my scenario.&nbsp;Thanks&nbsp;Akhil.

anonymous · Answer

Hi&nbsp;Akhil_1411&nbsp;,&nbsp;&nbsp;Very unclear what is the enf output you seek.&nbsp;The buckets are are changing when you select the category.&nbsp;&nbsp;&nbsp;Can you explain a little better&nbsp;&nbsp;Regards,Harsh Nathani&nbsp;&nbsp;&nbsp;

Forum Discussion

AllExcept column with slicer value

Related content

Allexcept - Remove slicer

Slicer for filtering several columns

ALLEXCEPT for multiple columns/Filters

Allexcept and slicer

Allexcept