Dynamic Grouping not working?

[Juju]

Hello again,

I am want to build a Debtors aging report based on the past blogs, but the dynamic grouping isn’t quite working and I am not sure why.

All the invoices are being put into the “Current” bucket, but, based off the aging there should be amounts in all the buckets, do you know what I am doing wrong? See the “grouped aging buckets” vs the aging classification using a switchtrue formula
Test Debtors Aging Report.pbix (11.7 MB)

Thanks for your time. I have read a couple of the answers on the dynamic groupings but they didnt quite help me out.

Dynamic grouping - 0-value in group not working

@dillenbram No problem. Answers to your questions below. I’ve also attached an updated pbix file at the end. Q1: Is it possible to add the missing AR Total Yes, it is. The measure logic just needs to be changed to work with 0 within the range. Receivables Per Group 2 = CALCULATE( [Invoice Values], FILTER( data_AR, COUNTROWS( FILTER( 'Aging Groups', IF( ( 'Aging Groups'[Min] < 0 |…

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() …

Juju

[EnterpriseDNA]

Hi @Juju, while waiting for other members to jump in, I suggest using the forum search to discover tutorial videos on dynamic grouping. You may find the videos here:

Search results for ‘dynamic grouping #enterprise-dna-online’ - Enterprise DNA Forum

Hope you find the solution you are looking for. Thanks

[pranamg]

The first Aging Buckets condition had 2 conditions, Days Over = 0 and Days Over >= 0. I removed the second condition. Check if this works for you now.

Aging Buckets = SWITCH(TRUE(),
             [Days Over] =0, "Current",
             AND([Days Over]>=1, [Days Over]<=30), "Age 1 -30 Days",
             AND([Days Over]>=31, [Days Over]<=60), "Age 31 -60 Days",
             AND([Days Over]>=61, [Days Over]<=90), " Age 61 -90 Days",
             "Age 90 Days +"
           )

[Juju]

Hi Pranamg .

Thanks for your reply! Sorry, I was a bit unclear, the switch true formula is putting the buckets correctly, this was a test measure to see if the “Receivables per Bucket” measure was working ok.

You can see in the left hand table that all the vouchers are being grouped as current using the
Receivables Per Group =
CALCULATE( ‘Aging Measures’[Amount],
FILTER( FactAccountsReceivable,
COUNTROWS(
FILTER( ‘Aging Groups’,
[Days over] <= ‘Aging Groups’[Min] &&
[Days over] >= ‘Aging Groups’[Max] ) ) > 0 ) )

• vs the aging on the right hand table.

Do you know why this would be?
Basically trying to recreate the dynamic grouping in Sam’s video per the links
Thank you!

[Juju]

HI @pranamg , did you happen to have any thoughts on the above? Ideally the table on the left would have 4 columns rather than everything being put into the “current” bucket? Thank you!

[Harsh]

Hello @Juju,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the criteria that you’ve specified. Below is the measure alongwith the screenshot of the final results provided for the reference -

Receivables Per Group = 
CALCULATE( [Amount] ,
    FILTER( 
        CROSSJOIN(
            VALUES( FactAccountsReceivable[Customer Key] ) ,
            VALUES( FactAccountsReceivable[Voucher] ) ) ,
        COUNTROWS(
            FILTER( 'Aging Groups' ,
                [Days over] >= 'Aging Groups'[Min] &&
                [Days over] <= 'Aging Groups'[Max] ) ) > 0 ) )

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Test Debtors Aging Report - Harsh.pbix (11.7 MB)

[Juju]

Ah grand, thank you very much!

[Harsh]

Hello @Juju,

You’re Welcome!!!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh