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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.