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.
Very likely this point has been discussed already, but could not find it. Averages stacked by categories are traps for people who do not know how to manage with DAX. May someone help on it?
The measure should be an average given by a DISTINCTCOUNT from Column1 divided by the number of days in range in the table, which I am getting by the DISTINCTCOUNT from a Date field
MyAvg = DISTINCTCOUNT(Column1_StuffToCount) / DISTINCTCOUNT(Column2_DateFromTable)
Y axis has the measure, MyAvg
X axis is made on Column3_ CategoryToXaxis;
bars (MyAvg) are stacked by using Column4_ToStackBars
Slicers give the users the ability to select the time period
Of course, the visual works fine when Count is presented. The Count is properly stacked. Data is correct. The problem comes when we want to present the average per category, because PBI calculates independent averages, per every “cell” that is in the chart, while I need the average be based on total days for the bar.
Assume we select 100 days from the slicer and assume that there was only one item in a day in slot “01:00” for category dark blue, PBI calculates the Avg = 1 (1 item / one day, although extraction period from slicer is 100 days), while the real average for that "cell" is 0.01. How can we force the measure in providing a correct Stacked Average? Tableau gives an easy set to define if calculation is computed per “cell” or per “pane” or per table.
In PBI Have tried the following, but in all cases the count of days is performed per every stack (per every cell in the chart) while I need the count be performed per bar
MyAvg =
VAR SelectedDays = CALCULATE( DISTINCTCOUNT(Tbl1[MyDate]), ALL(Tbl1[Date]) )
RETURN DISTINCTCOUNT(Tbl1[StuffToCount]) / SelectedDays
MyAvg =
VAR SelectedDays = CALCULATE( DISTINCTCOUNT(Tbl1[MyDate]), ALLSELECTED(Tbl1[Date]),
RETURN DISTINCTCOUNT(Tbl1 [StuffToCount]) / SelectedDays
MyAvg =
VAR SelectedDays = CALCULATE( DISTINCTCOUNT(Tbl1[StartDate]),
FILTER(ALL(Tbl1 [StartDate]), Tbl1 [StartDate] >= MIN(Tbl1 [StartDate]) ),
FILTER(ALL(Tbl1 [StartDate]), Tbl1 [StartDate] >= MAX(Tbl1 [StartDate]) ) )
RETURN DISTINCTCOUNT(Tbl1 [StuffToCount]) / SelectedDays
thanks
Solved! Go to Solution.
Alex, thanks. It moved me on the right path. I got the concept. ALLSELECTED has to indicate the field that has not to be broken down (as per name of the function). I have a case where the count depends by category on the X axis (which is the typical case in plotting stacked averages). The Average by category on X axis presented stacked by a further category has use the a count based on ALLSELECTED(filed that stacks the bars). Thanks for your help
Alex, thanks. It moved me on the right path. I got the concept. ALLSELECTED has to indicate the field that has not to be broken down (as per name of the function). I have a case where the count depends by category on the X axis (which is the typical case in plotting stacked averages). The Average by category on X axis presented stacked by a further category has use the a count based on ALLSELECTED(filed that stacks the bars). Thanks for your help
Does it help if you use just ALLSELECTED() without any table or column arguments?
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |