Grouping in calculated column

Hi all,

I have a more complex scenario in Grouping than the one in Sam’s tutorial.
In this case, grouping need to be done based on Order value, yet Sales table has multiple lines per order. So a virtual (?) table has to be created in the calculated column.
Managed to create a physical table (Order Groups) where each order is assigned to an Order Group (based on the supporting table ‘Order Value Groups’) but cannot get that calculation in the Sales table where is needed.

On the other hand, is it better performance-wise, to keep that info on a new table? In this case, what relationships should be created with the sales table?

All suggestions will be much appreciated.
Sample Report v4b.pbix (31.5 MB)

Hi @Thimios,

Give this a go. First create a measure for the order value.

Order Value = 
CALCULATE( [Sales],
    REMOVEFILTERS( Sales ),
    VALUES( Sales[Order#] )
)

Use that to calculate the Order groups.

Dynamic Grouping = 
CALCULATE (
    VALUES ( 'Order Value Groups'[Value Group] ),
    FILTER (
        'Order Value Groups',
        [Order Value] >= 'Order Value Groups'[Min Price] &&
        [Order Value] < 'Order Value Groups'[Max Price]
    )
)

That should get you the desired result, without creating a physical table in the model.

image

.
I hope this is helpful.

2 Likes

Thank you @Melissa,
That works but it does not help on calculating Order Value per Order Value Group. How would that be possible?
image

Hi @Thimios ,

Oh right, give this a go.

Dynamic Group value = 
CALCULATE (
    [Order Value],
    FILTER (
        VALUES ( Sales[Order#] ),
        COUNTROWS(
            FILTER( 'Order Value Groups',
                [Order Value] >= 'Order Value Groups'[Min Price] &&
                [Order Value] < 'Order Value Groups'[Max Price]
            )
        )   >0
    )
)

with this result.

image

I hope this is helpful.

1 Like

Hi @Melissa,

New measure works great, I would greatly aprreciate though if you could explain to me the Filter part:

VALUES ( Sales[Order#] ),
COUNTROWS(
FILTER( ‘Order Value Groups’,
[Order Value] >= ‘Order Value Groups’[Min Price] &&
[Order Value] < ‘Order Value Groups’[Max Price]
)
) > 0

I am trying to create another similar measure that counts number of orders within each Value Group so I need to understand the logic behind the above.

BR / Thimios

(Edited)

After reviewing the Dynamic Grouping via Supporting table and Measures session, I got a better understading of the logic. Yet applying that for Order Count doesn’t seem to provide the correct results:

image

Measure used is:

Dynamic Group count =
CALCULATE (
    [Orders],
    FILTER (
        VALUES ( Sales[Order#] ),
        COUNTROWS (
            FILTER (
                ‘Order Value Groups’,
                [Order Value] >= ‘Order Value Groups’[Min Price]
                    && [Order Value] < ‘Order Value Groups’[Max Price]
            )
        ) > 0
    )
)

Hi @Thimios,

Good to read you’ve found the Dynamic Grouping via Supporting table and Measures session. I also highly recommend examening the post by @Greg on Dynamic Grouping here.

There’s an easier way to calculate that, give this a go.

Dynamic Group count = 
VAR vTable =
    ADDCOLUMNS(
        VALUES ( Sales[Order#] ),
        "@Group", [Dynamic Grouping]
    )
RETURN

COUNTROWS( 
    FILTER( vTable,
        [@Group] IN VALUES( 'Order Value Groups'[Value Group] )
    )
)

I hope this is helpful.

1 Like

Hi @Melissa,

Where is [Dynamic Grouping] referring to?

The measure from post #2

Sorry for troubling you @Melissa, but this new version produces the same results as the first one. Am I not getting something here?

Dynamic Group count 2 =
VAR vTable =
    ADDCOLUMNS ( VALUES ( Sales[Order#] )@Group, [Dynamic Grouping] )
RETURN
    COUNTROWS (
        FILTER ( vTable, [@GroupIN VALUES ( ‘Order Value Groups’[Value Group] ) )
    )

Hi @Thimios,

Here’s my working example.
Sample Report v4b.pbix (31.5 MB)

Use this to back trace my steps. If you can’t figure it out, please post your updated file.
I hope this is helpful.

Couldn’t find out what went wrong, so I had to set it up from scratch.
Finally got it @Melissa, thank you!

1 Like