Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jasgun
Frequent Visitor

Group / Bins by measure

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:

image.png

 

 

 

 

 

 

 

 

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

image.png

 

 

 

 

 

 

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

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

image.png

 

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

image.png

 

 

 

 

 

 

 

 

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):

image.png

 

Any help will be very much appriciated.

 

Thanks

 

Jason

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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 +" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@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 +" )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jthomson
Solution Sage
Solution Sage

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.