Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
It will only open the column specified in the ALL function.
Hi,
Does this work?
=SUM(HABcs[CNT])/CALCULATE(SUM(HABcs[CNT]),ALL(HABcs[CheckFailed]))
Doesn't ALL break out to the whole table, and not restrict it to the current date grouping?
Hi,
It will only open the column specified in the ALL function.
That did it. Any pointers I can read up on as to why?
And thank you very much.
You are welcome. The ALL() function will clear the filter of the column provided as an input but within the current filter context only.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |