How to get slicer all selected items in Subtotals column cells with no associated data

[xiaohongt]

Hi,

I need to access a slicer's all selected items values in Subtotals column cells which has no row associated with some of the selected items, e.g. below screenshot, in the Build Slicer "2021_11_23" and "2021_10_23" are selected, in the matrix, build "2021_11_23" doesn't have "Airport" row/data underneath ( we also can see the black cells on "Airport" row are blank in the build "2021_11_23" column). In the black cells Subtotals column, how to get all selected builds in the slicer?  I tried AllSelected(build), build, in black cells only get "2021_10_23"

[Anonymous]

Hi @xiaohongt ,

Please refer to the following topic to see if it helps you.

https://community.powerbi.com/t5/Desktop/Table-subtotal-not-excluding-blank-values/td-p/1045145

If it does not help you, please provide your some sample data and  desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@xiaohongt , You need to share measures to check why is not showing sub total

You need a measure like

calculate([Measure], allselected(Table[Date]) )

or

calculate([Measure], filter(allsected(Table[Date]), [Build] =max(Table[Build]) && [Country] = max(Table[Country]  ) ))

Assumed build and country are on row

Also, check- Why grand total is wrong

https://www.youtube.com/watch?v=ufHOOLdi_jk

[xiaohongt]

@amitchandak , thank you, for the measure (Difference), I need to get the slicer all selected items, then use Build "2021_11_23" Total value minus Build "2021_10_23" Total value,  so far I'm not able to get Build "2021_11_23" is in slicer selected items in the measure for the Subtotal column black cells, only can get Build "2021_10_23" for slicer selected items in that cells, other Subtotal column cells I can get both selected builds, but in Subtotal column Black cells I can't,  (ignore the value for Subtotal column black cells in the picture which is not figured out yet because of this reason),  I need help how to get the slicer all selected items in the case of Subtotal column black cells? I tried ALLSELECTED(Table[Build]) and Table[Build], both only get Build "2021_10_23" which has associated data in the Subtotals column black cells, but I also need to get "2021_11_23" for the slicer selected Build