Segmentation on % for Weekly data

DAX DAX Calculations
1

Hi,

I want to do segmentation of weekly data based on % bin. I have looked at Sam’s video and tried it but not sure how it will work in my situation.

I have this data, I want to group them based on Range/No. Of Weeks and Average cases.

image

Like here: 20-40% happened 5 times and average cases sold is 3440.

image

Thanks,
RK

3

Hi @rit372002,

Fist I marked your Date table as a Date table.

Next there seems to be an issue with your VirtualRangeSelect table. The values do not correspond to the Range Names and the range from 40% ~ 50% is missing…

image

I lined up the values according to the Range names and created these measures.

No of Weeks = 
CALCULATE( DISTINCTCOUNT( 'Calendar'[Week Start Date] ),
    FILTER(
        VALUES( 'Calendar'[Week Start Date] ),
            COUNTROWS(
                FILTER( VirtualRangeSelect,
                    [% Students Virtual Only] >= VirtualRangeSelect[MinValueRange] &&
                    [% Students Virtual Only] < VirtualRangeSelect[MaxValueRange]
                )
            ) > 0
    )
)

.

Invoice Case total = 
CALCULATE(
    SUM( OBIQuery[Invoice Cases] ),
    FILTER(
        VALUES( 'Calendar'[Week Start Date] ),
            COUNTROWS(
                FILTER( VirtualRangeSelect,
                    [% Students Virtual Only] >= VirtualRangeSelect[MinValueRange] &&
                    [% Students Virtual Only] < VirtualRangeSelect[MaxValueRange]
                )
            ) > 0
    )
)

.
with this result (that matches the filtered table next to it)

image
image1017×235 14.8 KB

Here’s your sample file: Community - Modeling v2.pbix (667.0 KB)
I hope this is helpful.

.

BTW here’s a post by @Greg who gathered more details on this subject you can find that here.

2 Likes
4

Thanks @Melissa. It fit like a glove. I started with the same path but got jumbled up. Thanks for the wonderful solution.

Thanks,
-RK