topic Re: incorrect Total Selected Value alternative in DAX Commands and Tips

incorrect Total Selected Value alternative

NMOORE — Wed, 24 Jan 2024 11:23:59 GMT

Hi,

I need to sum quanities differently depending on my column values. The working example is much more complex with info I cannot share so I have drafted up a basic example using the Table below.

In the measure, I simply Sum up the quantities, but where the type is a Donkey I add the quanitites from Cow's (VAR _A).

Table

Type Quantity

Donkey	5
Donkey	5
Duck	3
Duck	5
Dog	4
Dog	5
Cow	3
Cow	5

Example Measure

VAR _A =

CALCULATE(

SUM('Example Table'[Quantity]),

FILTER(

ALL('Example Table'),

'Example Table'[Type] = "Cow")

)

VAR _B =

SUM('Example Table'[Quantity])

RETURN

IF(

SELECTEDVALUE('Example Table'[Type]) = "Donkey",

_A + _B,

)

Table Visual

Everything works how I want it to except the total is incorrect as I am using selected value (I underdstand why). Can anyone please assist? I cannot use a calculated column in the real example.

Thanks for any help.

Re: incorrect Total Selected Value alternative

olgad — Wed, 24 Jan 2024 13:22:43 GMT

Hi,
I dont know if it is gonna fit your real example,
but here it is

Example = VAR _A =

CALCULATE(

SUM('Example Table'[Quantity]),

FILTER(

ALL('Example Table'),

'Example Table'[Type] = "Cow")

)

VAR _B =

SUM('Example Table'[Quantity])

RETURN

If(HASONEVALUE('Example Table'[Type]), IF(

SELECTEDVALUE('Example Table'[Type]) = "Donkey",

_A + _B,

),_B+_A

)

Re: incorrect Total Selected Value alternative

NMOORE — Fri, 26 Jan 2024 16:42:32 GMT

Thanks so much Olgad, just away from my work at the moment I'll give it a whirl on Monday. Cheers

Re: incorrect Total Selected Value alternative

NMOORE — Tue, 30 Jan 2024 19:55:26 GMT

Hi Olgad,

Thanks again for the above. It solves the issue listed but I didnt distill the complexity of my issue into the example very well.

I'm hoping this might be more in line with my issue. If we use the same measure but using your method of HASONEVALUE there are more cases which need to be involved.

Because I need to Filter ALL on some tables I cannot then make it sum properly. I've been trying to create seperate measures and Sum them just in a total but the SelectedValue always follows.

RETURN

IF(

HASONEVALUE('Example Table'[Type]),

IF(

SELECTEDVALUE('Example Table'[Type]) = "Dog" ||

SELECTEDVALUE('Example Table'[Type]) = "Donkey",

_B / _A,

),_B / _A

)

I hope I'm missing something obvious. Let me know if you get the chance?

Thanks

Re: incorrect Total Selected Value alternative

olgad — Tue, 30 Jan 2024 21:24:41 GMT

Hi,

so, leave your first calculation like this

Example = VAR _A =

CALCULATE(

SUM('Example Table'[Quantity]),

FILTER(

ALL('Example Table'),

'Example Table'[Type] = "Cow")

)

VAR _B =

SUM('Example Table'[Quantity])

RETURN

IF(

SELECTEDVALUE('Example Table'[Type]) = "Dog" ||

SELECTEDVALUE('Example Table'[Type]) = "Donkey",

_B / _A,

)

Add one more measure:

Example2 =

SUMX(SUMMARIZE('Example Table', 'Example Table'[Type], "Example Correct", 'Example Table'[Example]), [Example Correct])

Re: incorrect Total Selected Value alternative

NMOORE — Wed, 31 Jan 2024 09:46:46 GMT

Much appreciated thanks. I've used Summarize to create tables in my data models but not wrapped inside SUMX like that so a very useful piece of learning, it worked in the large working model.

I tried to add the Example2 code as a RETURN - referencing Example as a variable (so it was all in the same measure) but the total was in the billions? I was looking at a neat way to put it all in one measusure but obviously not essential.

Thanks again

Re: incorrect Total Selected Value alternative

olgad — Wed, 31 Jan 2024 09:52:26 GMT

lately i had to correct totals a lot, just doest work out within a variable (that was my intention too to just have it in one place), alas, you have to create a second measure.

Re: incorrect Total Selected Value alternative

NMOORE — Wed, 31 Jan 2024 09:55:03 GMT

I've had to on some running totals as that's how it was done on some working examples online so I think it is the only way in some scenarios. Cheers

Re: incorrect Total Selected Value alternative

NMOORE — Tue, 19 Mar 2024 12:24:52 GMT

Hi Olgad,

Hope you're well.

I wondered if you knew what might be going wrong in a new scenario I have. Have you ever had only the grand totals wrong? I'm using HASONEVALUE to flick between different summarizes and everything is great accept the grand total (see pic). I'm aware this is limited info as I cannot share anything but thought it might be something you're used to seeing. I'm summarizing here against the periods/dates on the left...The grand total in the corner is off by circa £3m...

Thanks for any tips