Need Help With DAX Function (Displaying Buckets in Pivot Table)

[Bpark1994]

Hi Everyone,

I came across an issue that has left me stumped and I was hoping someone here would be able to point me in the right direction.

Say i have a table like this:

Worksheet Table:

Worksheet ID	Crew Day ID	Crew Name	Date	Revenue	Type of work	Revenue Buckets
1	1	Jon Doe	1/1/2018 12:00:00 AM	25	Easy	<=$50
2	2	Bill Smith	1/1/2018 12:00:00 AM	50	Medium	<=$50
3	1	Jon Doe	1/1/2018 12:00:00 AM	75	Hard	<=$100
4	1	Jon Doe	1/1/2018 12:00:00 AM	100	Easy	<=$100
5	2	Bill Smith	1/1/2018 12:00:00 AM	60	Medium	<=$100
6	4	Patrick Star	1/1/2018 12:00:00 AM	30	Hard	<=$50
7	5	Jimmy Parker	1/2/2018 12:00:00 AM	90	Easy	<=$100
8	6	Brian Jalette	1/2/2018 12:00:00 AM	120	Medium	<=$150
9	3	Jon Doe	1/2/2018 12:00:00 AM	40	Hard	<=$50
10	4	Patrick Star	1/1/2018 12:00:00 AM	80	Easy	<=$100

As you can see a crew can submit multiple worksheets per day so to find the unique number of crew days I created the following DAX function:

                       Crew Days DAX:=CALCULATE(DISTINCTCOUNT([Crew Day ID]))

Now what I want to do is display it in a pivot table and have it look like the following:

Buckets	Crew Days DAX
<= $50	1
<= $100	1
<=$150	3
>$150	1

However when I create the revenue buckets as a calculated column on the Worksheets Table and put it as the rows of my pivot table and put my Crew Days DAX function as the values i get the following:

Buckets	Crew Days DAX
<= $50	4
<= $100	4
<=$150	1
Grand Total	6

I now understand that the reason its giving me this result is its running my DAX function on the filtered set of data from the bucket calculated column but how do I get the result I want? Also if I wanted to determine average daily revenue by Type of Work how would I go about that?

Thanks for all the help!

[parry2k]

What logic you are using drive following result:

Now what I want to do is to be able to create some revenue buckets based on the daily revenue and display it in a pivot table. It should look like the following:

[Bpark1994]

I cleaned that sentence up. Does it seem clearer now? Basically I'm trying to get that result from the table I have. Is that possible? Is there any workarounds?

[Shantanu00007]

Hello dear,

It can be done without any DAX . Please follow below steps to dynamically create rev buckets
1. Go to edit queries
2. Select rev bucket column and right click on it and select last option Add as a new query.
3. A new list will be generated. Select column in new list , right click and select remove duplicates.
4. Again right click and select "To Table"

5. Make a relationship between voth the tables . It will be a one to many relationship.

6. Use rev buckets from new table and crew id from main table.
7. Select count(distinct) in values for crew id.

Hope this solves your problem.

Answer to your second question.
Select type of work and revenue in table visual.
Select average instead of sum in values.

[Bpark1994]

Hi everyone,

I posted this the other day but the responses seem to have died out so I thought i would repost it. Any help is appreciated.

Thanks!

https://community.powerbi.com/t5/Desktop/Need-Help-With-DAX-Function-Displaying-Buckets-in-Pivot-Tab...

[v-piga-msft]

Hi @Bpark1994,

I'm a little confused about your desired output, could you describe your logic in more details?

Best  Regards,

Cherry

[Bpark1994]

The desired output is the second table listed that looks like this:

Buckets	Crew Days DAX
<= $50	1
<= $100	1
<=$150	3
>$150	1

The logic to arrive at this result is as follows:

If you add all the revenue for crew day ID 1 you get 200. 200 would fall into the >$150 bucket so that would be the 1 in that category in the table above. 

Just not sure how to arrive here through a dax function

Thanks!

[Anonymous · Accepted solution]

Hi,

You could do this using a second calculated column that sums the Revenue without filtering anything other than the Crew Day ID.  This will work out the total Revenue Bucket for each Crew Day ID. 

Use a calculated column formula like this:

Revenue Buckets Aggregated =
IF( CALCULATE(SUM(Table1[Revenue]), ALLEXCEPT(Table1, Table1[Crew Day ID])) <= 50, "<=$50",
    IF( CALCULATE(SUM(Table1[Revenue]), ALLEXCEPT(Table1, Table1[Crew Day ID]))  <= 100, "<=$100",
        IF( CALCULATE(SUM(Table1[Revenue]), ALLEXCEPT(Table1, Table1[Crew Day ID]))  <= 150, "<=$150",
            IF( CALCULATE(SUM(Table1[Revenue]), ALLEXCEPT(Table1, Table1[Crew Day ID]))  > 150, ">$150"))))

Then your Crew Days DAX measure will work using this column.

Then to work out the averages, just create a simple measure like and put it in a table with the Type of work:

      Average Daily Revenue = CALCULATE(AVERAGE(Table1[Revenue]))

You should hopefully get something like my results in the screenshot....

[Bpark1994]

Hi,

Thanks for the suggestion but this resulted in the same wrong pivot table. Any other ideas?