AllExcept column with slicer value

[Akhil_1411]

Hi,

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.  Can anyone please help in this.

Dax which i was trying:-

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 c

Note: I need AllExcept at column is because i need to create buckets based on this results.

Power BI File: -https://we.tl/t-VttSGqFMpX

Thanks,

Akhil.

[harshnathani]

Hi @Akhil_1411 ,

You are creating too many columns which may not be the right approach.

Use the filter context of Power BI Engine.

i have created most of your queries via measures.

See image

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]>0.0 && [Measure Profit Ratio]<=0.05,"0%-5%",
[Measure Profit Ratio] <=0.1,"0%-10%",
[Measure Profit Ratio] <=0.2,"10%-20%",
[Measure Profit Ratio]<=0.5,"20%-50%"
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[Akhil_1411]

@harshnathani ,

The reason why iam creating the column is to fix at the cuatomer level profit or sales and then calculate the bucket size.

And this bucket should dynamically change when i select any slicer value in category.This is my requirement

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.

Hope you understood my scenario.

Thanks 

Akhil.

[harshnathani]

Hi @Akhil_1411 ,

Very unclear what is the enf output you seek.

The buckets are are changing when you select the category.

Can you explain a little better 

Regards,

Harsh Nathani

[Akhil_1411]

Hi,

Let me put this in a simple way.

I am trying to acheive the below Profit bucket table(power BI) in the form of graph attached is the screenshot and the power BI file ( Buckets are created on measures)

Power Bi File:- https://we.tl/t-HzZ46RQiNr

[harshnathani]

Hi @Akhil_1411 ,

Create a new Table

Table = ADDCOLUMNS(
    ADDCOLUMNS(
    SUMMARIZE(Orders,Orders[Customer Name],"Total Profit", SUM(Orders[Profit]), "Total Sales", SUM(Orders[Sales])),
    "Profit Ratio" , DIVIDE([Total Profit],[Total Sales],0)),
    "Profit Bucket",
     switch(TRUE(),
               [Profit ratio]>0.0 && [Profit ratio]<=0.05,"0%-5%",
               [Profit ratio]>0.5 && [Profit ratio]<=0.1,"5%-10%",
               [Profit ratio]>0.1 && [Profit ratio]<=0.3,"10%-30%",
               [Profit ratio]>0.3 && [Profit ratio]<=0.5,"30%-50%",
               [Profit ratio]>0.5,"50+%")
)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Akhil_1411]

Hi @harshnathani ,

But when i create a new table. Let's say if i wanted to slice down with other dimensions (like segments or order year) it will not work right?

Thanks,

Akhil.

[harshnathani]

Hi @Akhil_1411 ,

You data models are not correct in the pbix file.

Create a Date Table,Customer Table seperately, Category Table separately.

https://www.youtube.com/watch?v=Qy1FiR2Eby8

https://blog.enterprisedna.co/segment-your-customers-into-groups-advanced-dax-example/

https://www.youtube.com/watch?v=iH3gW_vy69o

https://www.youtube.com/watch?v=jLloQ_1KiGI

Regards,

Harsh Nathani

[amitchandak]

@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]

Not getting the right result @amitchandak