Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I have a large model with invoices, spend, categories and supplier names etc.
I would like to create a stacked column chart where I can group the suppliers in user defined bins by spend. Such as 0-1000$, 1001-5000$ etc. Which bin the supplier gets should vary based on what spend that are currently filtered.
Right now I have tried to use a SWITCH function in a measure which defines the bins. Then I create a calculated column where I put this measure, giving each supplier a bin. The problem is that the result in this calculate column doesn’t change when I filter in the report, eg to a specific month or a category.
Does anyone have a tip or a solution to this problem? How do I make calculation of user defined bins that change in the current filter context?
I have tried many different ways via DAX but I can't get it to work properly.
Thanks in advance!
Solved! Go to Solution.
Thank you for your answer!
I was going to upload sample data and expected outcome today but now I found a solution on my own.
As you answered, I realized that and that there were no dax-workaround to make a calculated column out of a measure.
The solution for me was to work out a way with a small separate table where I could put the user defined bins as a column (and then place it on category-level in a visual) and then work out the number of suppliers in each bins as a measure.
Pages like this helped me to build my solution:
https://radacad.com/dax-measure-in-the-axis-of-the-power-bi-report
Thank you for your answer!
I was going to upload sample data and expected outcome today but now I found a solution on my own.
As you answered, I realized that and that there were no dax-workaround to make a calculated column out of a measure.
The solution for me was to work out a way with a small separate table where I could put the user defined bins as a column (and then place it on category-level in a visual) and then work out the number of suppliers in each bins as a measure.
Pages like this helped me to build my solution:
https://radacad.com/dax-measure-in-the-axis-of-the-power-bi-report
"Then I create a calculated column where I put this measure"
As you have noticed - you cannot make a calculated column out of a measure. All your dynamic binning has to be entirely based on measures.
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |