topic Display totals for parent item(s) in hierarchy when child selected in DAX Commands and Tips

Display totals for parent item(s) in hierarchy when child selected

tnwcloud — Mon, 09 May 2022 05:08:39 GMT

Working with a geo hierarchy Region>State>City. Have a measure that calculates Average Cost. Customer wants a report that allows them to select a City using a slicer and see the average cost for each level of the heirarchy on three separate cards. So in other words if they select 'Albany' in the slicer, the City card would show Average Cost for 'Albany', the State card for 'New York', and the Region card for 'Northeast'.

I've tinkered with VALUES and ALLEXCEPT thinking that I need to remove the lower hierarchy filters but I just can't get it. Any ideas?

Re: Display totals for parent item(s) in hierarchy when child selected

amitchandak — Mon, 09 May 2022 05:40:56 GMT

tnwcloud , Measure for state , Assume you already have Avg Cost Measure

Meausre =

var _tab = summarize(allselected(Geo), Geo[State])

return

calculate([Avg Cost), filter(all(Geo), Geo[State] in _tab ) )

Meausre =

var _tab = summarize(allselected(Geo), Geo[Region])

return

calculate([Avg Cost), filter(all(Geo), Geo[Region] in _tab ) )

Re: Display totals for parent item(s) in hierarchy when child selected

tnwcloud — Tue, 10 May 2022 04:57:00 GMT

Thanks amitchandak it worked for Region but not for State. Any idea why it might work for the top of the hierarchy but not lower down?

Re: Display totals for parent item(s) in hierarchy when child selected

Anonymous — Thu, 12 May 2022 07:51:23 GMT

Hi tnwcloud ,

I created some data:

Here are the steps you can follow：

1. Create measure.

City_Avg = var _sleect=SELECTEDVALUE('Table'[City]) return CALCULATE(AVERAGE('Table'[Amount]),FILTER(ALL('Table'),'Table'[City]=_sleect))

State_Avg = var _select=SELECTEDVALUE('Table'[City]) var _status=CALCULATE(MAX('Table'[State]),FILTER(ALL('Table'),_select in SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[State]=EARLIER('Table'[State])),"1",[City]))) return CALCULATE(AVERAGE('Table'[Amount]),FILTER(ALL('Table'),'Table'[State]=_status))

Region_Avg = var _select=SELECTEDVALUE('Table'[City]) var _status=CALCULATE(MAX('Table'[State]),FILTER(ALL('Table'),_select in SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[State]=EARLIER('Table'[State])),"1",[City]))) var _region=CALCULATE(MAX('Table'[Region]),FILTER(ALL('Table'),_status in SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Region]=EARLIER('Table'[Region])),"1",[State]))) return CALCULATE(AVERAGE('Table'[Amount]),FILTER(ALL('Table'),'Table'[Region]=_region) )

2. Result:

Best Regards,

Liu Yang

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