topic Re: How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item. in DAX Commands and Tips

How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item.

Anonymous — Fri, 14 Jan 2022 11:21:12 GMT

How to get this measure on the card total? I am only looking to get 27 on card visual but it comes blank. I tried aggregation on top of this "Rank Ongoing Selected" but it did not work.

I am looking to show Rank of Avg Ongoing Cost where Z_highlight = 1. There are also slicer changing the filter context hence I am using ALLSELECTED for Rank.

Avg Ongoing Ranking 1 = RANKX(FILTER(ALLSELECTED(ValueMoney[Fund Provider]),

NOT(ISBLANK(ValueMoney[avg ongoing cost]))),

[avg ongoing cost],,ASC)

Rank Ongoing Selected = VALUE( IF([Z_Highlight] = 1, RANKX(FILTER(ALLSELECTED(ValueMoney[Fund Provider]),

NOT(ISBLANK(ValueMoney[avg ongoing cost]))),

[avg ongoing cost],,ASC), BLANK()))

Re: How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item.

amitchandak — Fri, 14 Jan 2022 11:53:23 GMT

Anonymous , assume you create a rank like

Rank Brand = Rankx(ALL('Item'[Brand]),[Net]) // using all

Then this measure can give selected rank for brand

Rank M = maxx(VALUES('Item'[Brand]), [Rank Brand])

Re: How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item.

Anonymous — Fri, 14 Jan 2022 11:58:37 GMT

Thanks for the response Amit. but if we use ALL in RANK then we will lose the filter context set by the slicers. There are slicers on page that reduces the data set to few brands only. Rank is calculated based on selected values.

Re: How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item.

amitchandak — Fri, 14 Jan 2022 12:22:55 GMT

Anonymous , That is a problem, when selecting a brand in visual, you have only that brand. so allselected will be given Rank 1.

Interaction off - it will not work

removefilters, all I tried a few more combinations, but they remove the brand from the context. So did not work for me.

Wait for other users to provide some solution

Re: How to get Total for a measure column or an alternate DAX solution? Rank of Total for selected item.

Anonymous — Fri, 14 Jan 2022 13:17:59 GMT

I just found the solution, sharing for the community. The final step was to get Rank 27 from the last column onto the card. I am using the below DAX to create a final summary of the last column.

Avg Ranking Ongoing Cost Sum = CALCULATE(SUMX(VALUES(ValueMoney[Fund Provider]),

[Rank Ongoing Selected]),ALLSELECTED(ValueMoney))