topic Re: Total not displaying in Row in DAX Commands and Tips

Total not displaying in Row

thurlowj — Fri, 05 Apr 2024 13:15:54 GMT

Hi! I think I'm missing something obvious but I have tried so many different measures and none of them have worked and honestly getting no where each time I look at it. I want to see the Total display under the 'Total' row and I can't get it right. Could someone help?

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 13:39:33 GMT

If you could show the measure it would help a lot 😉

Re: Total not displaying in Row

AnalyticPulse — Fri, 05 Apr 2024 13:45:15 GMT

have you enables total option in format pane in your visual settings?

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 13:50:26 GMT

obviously, since there's the text "Total".

Re: Total not displaying in Row

thurlowj — Fri, 05 Apr 2024 13:54:21 GMT

DCOH Mean = calculate(Geomean('Table'[ DCOH ]), FILTER('Table', 'Table'[Locid]= selectedvalue('Table B'[Locid])))

Here's the measure for my DCOH Mean calculation.

I tried several measures to get the sum to generate for my selected entries but it adds a column.

Obviously new to this, but this is my first attempt of using slicers so I can compare different selected data. Not sure if that has anything to do with the total number not displaying though.

Re: Total not displaying in Row

thurlowj — Fri, 05 Apr 2024 13:55:44 GMT

I wish this was the solution!

Re: Total not displaying in Row

AnalyticPulse — Fri, 05 Apr 2024 13:58:34 GMT

you are using selectedvalue in your measure maybe your measure is returning the values as text and if there is blank value in your data then that blank values is being shown on the total as the total ot text field is always text .

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 13:58:42 GMT

The problem could be SELECTEDVALUE.

In the Total Row, SELECTEDVALUE returns BLANK() because there are more then "one" values selected.

What's "Table B"? Can you show the relationships?

Re: Total not displaying in Row

thurlowj — Fri, 05 Apr 2024 14:02:21 GMT

Thanks for your help so far, that makes sense. 'Table B' contains my Locid selections

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 14:06:33 GMT

Workaround could be:

DCOH Mean = VAR IsTotal = NOT (HASONEVALUE('Table B'[Locid])) VAR SingleVal = CALCULATE ( GEOMEAN ( 'Table'[ DCOH ] ), FILTER ( 'Table', 'Table'[Locid] = SELECTEDVALUE ( 'Table B'[Locid] ) ) ) VAR TotalVal = GEOMEAN('Table'[DCOH]) RETURN IF(IsTotal, TotalVal, SingleVal)

BUT I don't understand why you do it this way in the first place. Just make a relationship between Table 2[Locid] (1) to Table[Locid] (m) (1:m) and alter your measure to:

DCOH Mean = GEOMEAN ( 'Table'[ DCOH ] )

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 14:11:01 GMT

no.

Re: Total not displaying in Row

thurlowj — Fri, 05 Apr 2024 14:12:29 GMT

Wow. I was over complicating things for myself, huh? Thank you so much, I went with the second solution. I really appreciate your help with this!

Re: Total not displaying in Row

lukiz84 — Fri, 05 Apr 2024 14:15:11 GMT

you're very welcome. i've been there myself, overcomplicating stuff 🙂