Average Stacked

[RRSSDW]

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

[RRSSDW · Accepted 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

  

[AlexisOlson]

Does it help if you use just ALLSELECTED() without any table or column arguments?