Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lucas105
Advocate II
Advocate II

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 
 
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @lucas105 

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

b1.png

 

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:

b2.png

 

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.

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @lucas105 

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

b1.png

 

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:

b2.png

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.