Group / Bins by measure

[jasgun]

Hi,

I have what I thought would be a simple request.

I need to create a new field that groups the calculated measure "Limit Amount Our Share" in to various items.

The original data contains something like the below:

I created a summary of the above (just taking out the middle column):

Based on the "Prog/Pol Ref" attribute I wanted to create groupings.

Initially I thought I would create an if statement like the below: 

However I get the results below, that base the groupings including Policy Reference

The results I wanted was something like the below (Sorry about the red scribble, first two items 9 - 9.99m, last one 4 - 4.99m):

Any help will be very much appriciated.

Thanks

Jason

[v-chuncz-msft · Accepted solution]

@jasgun,

You need to add a measure instead.

Measure =
VAR q =
    QUOTIENT ( SUM ( Table1[Limit Amount Our Share] ), 1000000 )
RETURN
    IF ( q < 10, q & " - " & q & ".99m", "10m +" )

[jthomson]

How exactly are you getting to the second image you see? Is it just the output of a visual? I'm guessing that the new column you're making is applying things on a row by row basis, which won't recognise that things will be summed in a later step.

I'd try to look to group your data in Power Query by your project reference and have it sum up the limit amount in the same process, your measure to group the values should then work as everything is in one row

[jasgun]

The first & second images are just outputs from the visual.

Yes the new column is on a row by bow basis.

I did look at the group by in the query, but it errored the whole table.

I will revisit it.