Weighted percentage within the Date Hierarchy

[jader3rd]

I have a table where the columns are TimeSlice, CheckFailed and CNT. TimeSlice is a timestamp aggregated to the hour, CheckFailed is a label, and the CNT is the count of faile checks for the TimeSlice/hour.

In a Matrix, when I use the TimeSlice as the Rows, CheckFailed as the Columns I want the weighted percentage to be the value. So for the day Day row the WeightPct should be the percentage of the count the given CheckFailed had for the day, and for the Month row the WeightPct should be the percentage of the count the given CheckFailed had for the Month. Same goes for Quarter and Year.

I do this in SQL with

SUM(CNT) \ SUM(CNT) OVER (PARTITION BY CONVERT(DATE, TimeSlice)) AS WeightedPct

.

The closest I can get in DAX is

WeightedPct = SUMX(HABcs, [CNT]) / SUMX(FILTER(HABcs, HABcs[TIMESLICE]), SUM(HABcs[CNT]))

What I need is the sum of all CNT's for the Scope (all CNT's for a specific CheckFailed in a given TimeSlice grouping (day or month)), divided by the sum of all CNT's for the given TimeSlice grouping regardless of CheckFailed.

[Ashish_Mathur]

Hi,

Does this work?

=SUM(HABcs[CNT])/CALCULATE(SUM(HABcs[CNT]),ALL(HABcs[CheckFailed]))

[jader3rd]

Doesn't ALL break out to the whole table, and not restrict it to the current date grouping?

[Ashish_Mathur · Accepted solution]

Hi,

It will only open the column specified in the ALL function.

[jader3rd]

That did it. Any pointers I can read up on as to why?

And thank you very much.

[Ashish_Mathur]

You are welcome.  The ALL() function will clear the filter of the column provided as an input but within the current filter context only.