Grouping via Calculated Column issue

[Krays23]

HI,

Im following Sam`s example in the video in DAX Formula Pattern training on Grouping via calculated columns.

Can some one tell me why mine doesnt work ive followed the same example step by step but my table doesnt filter based on the low Mid High slicer all the values are the same???

Thanks

Dan

DAX Formula Patterns.pbix (1.1 MB)

[AntrikshSharma]

Because your Pricing Group column doesn’t have a relationship with Sales table.

If you want your measure to work in the current scenario with the current arrangement intact then use:

Total Sales = 
CALCULATE ( 
    SUM( Sales[Total Revenue] ),
    TREATAS (
        VALUES ( 'Groups For Pricing'[Pricing Group] ),
        Sales[Pricing Groups] 
    )
)

Or just create a relationship between Sales and Pricing Group and then everything works, but you still will have to make a change to the calculated column’s code in order to be able to create a relationship, otherwise Sales table would depend on ‘Groups For Pricing’ table for the Pricing Group column and ‘Groups For Pricing’ table will depend on Sales table for blank row, creating circular dependency.

Pricing Groups =
CALCULATE (
    DISTINCT ( 'Groups For Pricing'[Pricing Group] ),
    FILTER (
        'Groups For Pricing',
        Sales[Total Unit Price] >= 'Groups For Pricing'[Min Price]
            && Sales[Total Unit Price] < 'Groups For Pricing'[Max Price]
    )
)

[Melissa]

@Greg has also put together a post on dynamic grouping, you can find that here:
It also includes links to relevant content on this subject, so you might want to explore that as well.

Dynamic Grouping

Grouping in Power BI is often referred to as segmentation, chunking, binning, banding, or bucketing. For the purposes of this article, all will be referred to as the Dynamic Grouping DAX pattern. For example, you may want to know the group totals of aged invoices in a certain time period. So, first ensure your grouping column is available in the fact table (e.g., Invoices[Aging Days]). Aging Days = SWITCH( TRUE(), Invoices[Paid in Full] = 1, 0, Invoices[Paid in Full] = 0, INT( TODAY() …

[Krays23]

Thanks @AntrikshSharma

Sam’s video doesn’t have a relationship with the sales table either though in his tutorial video 3 minutes 32 seconds in

Grouping with calculated column

[EnterpriseDNA]

Hi @Krays23, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[EnterpriseDNA]

Hi @Krays23, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[Krays23]

Not really doesn’t explain why in sams video he doesn’t use relationships

[Mark]

Hi @Krays23,

You simply need to swap your Pricing Group column in your slicer and matrix table to use the Pricing Group column from your [Sales] table. Currently you are using the Pricing Group column from your [Groups For Pricing] table. As this has no physical relationship with your sales table, no change occurs.

If your Sales fact table was very large I would suspect that creating the physical relationship between table [Groups For Pricing] and [Sales] would perform better, as the slicer would simply be reading 3 records rather than having to check every distinct value in [Sales] before it can be populated.

I hope this helps,

Mark