Forum Discussion

NMOORE's avatar
NMOORE
Icon for Helper II rankHelper II
2 years ago
Solved

incorrect Total Selected Value alternative

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. ...
  • olgad's avatar
    olgad
    2 years ago

    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,
            0
        )

    Add one more measure:
    Example2 =

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

     

     
NMOORE's avatar
NMOORE
Icon for Helper II rankHelper II
2 years ago

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,
        0
    ),_B / _A
    )
 

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

 

Thanks

olgad's avatar
olgad
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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,
        0
    )

Add one more measure:
Example2 =

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

 

 
  • NMOORE's avatar
    NMOORE
    Icon for Helper II rankHelper II
    2 years ago

    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

    • olgad's avatar
      olgad
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      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.

      • NMOORE's avatar
        NMOORE
        Icon for Helper II rankHelper II
        2 years ago

        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