Forum Discussion

NMOORE's avatar
NMOORE
Helper 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.

 

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

Donkey5
Donkey5
Duck3
Duck5
Dog4
Dog5
Cow3
Cow5

 

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,
        _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.

 

  • 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])

     

     

8 Replies

  • olgad's avatar
    olgad
    Resident Rockstar
    2 years ago

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

     

    • NMOORE's avatar
      NMOORE
      Helper II
      2 years ago

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

    • NMOORE's avatar
      NMOORE
      Helper 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
        Resident 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])