Use DAX to Bin the output of a measure

DAX DAX Calculations
1

I have a situation where i am connected to a dataset that I cannot add columns or tables to because it is an enterprise wide dataset. I have created a cumulative total measure that I now need to break out into buckets so i can add a multiplier to the total to get a rebate number.

For example:
I have a cumulative total of $7,000,000.00, I need to “bucket” this value into $2,000,000.00 groups.

Level 1 $0 - $2,000,000.00 will have a multiplier of .005
Level 2 $2,000,000.01 - $4,000,000.00 will have a multiplier of .01 but only on the $2,000,000.00 in this bucket
Level 3 $4,000,000.01 - $6,000,000.00 will have a multiplier of .015 but only on the $2,000,000.00 in this bucket
Level 4 $6,000,000,01 and up will have a multiplier of .02 but only on the dollars above $6,000,000.00 in this bucket

I then need to show the result of each “Bucket” in a visual, or multiple visuals. What is the best way to accomplish this task?

Thank you in advance,

1 Like
2

Hi @jamesg. A work-in-progress PBIX and an example in Excel of what you’re looking for would help the forum members visualize your issue and desired outcome.
Greg

1 Like
3

Hi @jamesg

Make sure you drink a cup coffee before start, and you DAX Level is good.

Please check this video

and

1 Like
4

@jbressen Thank you!

I have just watched the progressive video and it will do what I need. I am going to familiarize myself with all the DAX (you are right, it is a lot).

How would I also show a running total for each discount bucket? for example, the first bucket is easy because it is $0, the second bucket has a potential value of $1,649.89. can i show a bar graph with the running total of that potential? i would like to show it for all buckets.

1 Like
5

@jbressen.

Can I do this calculation using a measure, or multiple measures? As stated in the original post, I am using a locked down dataset and I am not able to add columns or calculated columns.

6

Hi @jamesg

For your requirement, I believe you will need to create separate Measures for each Bucket and show in the visual like I created for Bucket 1 and Bucket 2. Calculations are just representative, can modify as per requirement.

Bucket 1 =
VAR MinBuck1 = 0
VAR MaxBuck1 = 2000000
VAR MinBuck2 = 2000001
VAR MaxBuck2 = 4000000
VAR MinBuck3 = 4000001
RETURN
    IF (
        [Total Sales] >= MaxBuck1,
        MaxBuck1 * 0.005,
        IF ( [Total Sales] > MinBuck1, [Total Sales] - MinBuck1 * .005, BLANK () )
    ) 

Bucket 2 =
VAR MinBuck1 = 0
VAR MaxBuck1 = 2000000
VAR MinBuck2 = 2000001
VAR MaxBuck2 = 4000000
VAR MinBuck3 = 4000001
RETURN
    IF (
        [Total Sales] >= MaxBuck2,
        MaxBuck2 * 0.002,
        IF ( [Total Sales] > MinBuck2, [Total Sales] - MinBuck2 * .002, BLANK () )
    )

Buckets

Let me know if this is helpful or if require any other help.

Thanks
Ankit J

1 Like
7

Hi @jamesg did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @jamesg, we’ve noticed that no response has been received from you since the 20th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @jamesg, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!