Segmentation on % for Weekly data

DAX DAX Calculations
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