Forum Discussion

lucas105's avatar
lucas105
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Handling multiple ifs with subtotals

Hi all, 

 

I have a measure that should only be calculated if an attribute is chosen and I have some issues trying to implement it. 

 

The requirement is that I should display a message like "Select a attribute" in case the field is not chosen. 

 

So what I've been working with was: 

 

IF(
   SUM(Table[Column])>0, 
   IF(
       ISFILTERED(Table[Attribute]),
       SUM(Table[Column]), 
       "Select a attribute"
   )
 
And this has been working well, but we the measure got even more complex and I've spend quite some time trying to figure out how to handle this with additional logic in there. 
 
Can you help me out on how to handle subtotal in such way that I'll still be able to display message, but not show it on total row? 
 
Total row should be calculated only if we have one value of an attribute filtered out. 
 
Thanks,
Luc
 
However this wasn't working very well since it'd display the message on the subtotal, so 
 
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, lucas105 

    Based on your description, I created data to reproduce the scenario.

     

    You may create a measure as follows.

    Measure = IF(

       SUM('Table'[Column])>0,

       IF(

           ISFILTERED('Table'[Attribute]),

           SUM('Table'[Column]),

           IF(

                COUNTROWS('Table')=CALCULATE(COUNTROWS('Table'),ALL('Table')),

                SUM('Table'[Column]),

                "Select a attribute"

            )

       )

       )

     

    Result:

     

    If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you.

     

    Best Regards,

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, lucas105 

    Based on your description, I created data to reproduce the scenario.

     

    You may create a measure as follows.

    Measure = IF(

       SUM('Table'[Column])>0,

       IF(

           ISFILTERED('Table'[Attribute]),

           SUM('Table'[Column]),

           IF(

                COUNTROWS('Table')=CALCULATE(COUNTROWS('Table'),ALL('Table')),

                SUM('Table'[Column]),

                "Select a attribute"

            )

       )

       )

     

    Result:

     

    If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you.

     

    Best Regards,

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • lucas105's avatar
      lucas105
      Icon for Advocate II rankAdvocate II
      6 years ago

      v-alq-msft

       

      thank you for your reply, but that's not exactly what I wanted to achieve. 

       

      My desired result would be that you wouldn't display a Total value at all if there's no attribute selected. I have values that are not additive across those attributes.

       

      So if you'd choose a B, you'd see both row with B value and Total with same value. 

       

      If you'd add Attribute and a Metric summing Values to the table visual, you'd see "Select an attribute" in row values and in total row, you can either show same or nothing at all. 

       

      Best,

      Luc