Need to Slice a Calculated Column and can't get it to work with Measures

[DataM]

Hello! This is my first time posting here excited to be part of the community! I have an issue I really need help worth

I really need help with the below I've been having an issue with how slicers don't affect calculated columns (which I do know) and am trying to understand how to work around this. Everybody says use measures but it's not so straightforward for my application. We have customer data and order data (two different tables) and we wanted to group the customers into deciles based on spend ranking and see how much spend is coming out of that decile. Example if there's 100 customers we just rank them and put each 10 in a bucket and add the spend coming out of each bucket. We were able to get that done with calculated columns  on the chart below

 

The big question though that we were not able to figure out and I spent hours and hours trying to figure this out is: What happens if we want to calculate the deciles based on a different date context (rather than the whole dataset), so essentially if we wanted to put a slicer and rank the customers based on say, 2021 only and then dynamically calculate who is in what decile based on the new context. Since slicers don't affect calculated fields that doesn't work, and then if I try to use measures of any sort to calculate tiers and I put it on a graph it will always collapse to one value (it doesn't force it at the customer scale), UNLESS you have customers on the view which obviously doesn't actually make sense for this graph. I also have another table that I would want to be able to filter by we used to use Tableau before and I know that this would be easily doable in Tableau so I am hoping there's an easy way here that I'm just not seeing.

For those familiar with Tableau, there is an option there to have a filter and "add to context" where you have a regular filter that essentially preempts calculations. If Powerbi has something like that it would be great or really I just need a way to work the above through measures and I don't know how. 

Thanks in advanced!

[goncalogeraldes]

Hello and welcome to the community! A few questions for you: What are the connections between the two tables in the model? What date field are you currently using? In which table is the date field? Do you have a date dimension to use as the common link for time intelligence filtering? 

[DataM]

Thanks for the quick response!

The relevant fields are really just as follows

Order Table:

1. Order ID

2. Order Sales Price

3. Order Date (This is the date field being used) 

4. Customer ID (Linked to Customer Table Many to 1)

Customer Table:

1. Customer ID (Linked to Order Table 1 to many)

2. Customer Email (and other customer specific info)

3. Calculated column that calculates total order $  against this customer (using a calculate function and an ALLEXCEPT filter) <--this is the column in question. 

There's no date dimension that's common because there's no real dates in the customer table. I actually don't even really need the customer table that much I initially had it all in one table but realized it was much harder todo this that way because I want to sum the orders against each customer so I just split it instead of doing a summary table. 

[goncalogeraldes]

The problem with using calculated columns is that they are not dinamically affected by filter context. So in this case, when to try to drill-down on the data it won't perform any dinamyc calculations on the dataset and it will keep the original values in the table. If you want this kind of flexibility, specially when using PBI's default time intelligence, you should use measures. They can meet almost every requirement! Most of the times, when creating measures that require IF statements or complex conditions, try using variables to enable the formula to iterate through the dataset. If you need, you can send a sample formula that you used in the calculated column! You can also find some help on creating measures with variables in the following link:

https://docs.microsoft.com/en-us/power-bi/guidance/dax-variables

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes