using sum formula to add only the rows visible in the table

[rwong1]

I'm trying to add a measure and display it via a card to sum only the rows that are visible in the table PLUS another total from another table.  The rows are shown based on what I selected as a prospect code.  However, I excluded some rows that have the same prospect code with a filter because I don't want those to show up in the table.  I don't want Power Bi to add the excluded transactions but apparently it is doing that right now.  I only want it to add rows that are visible on the table.  As you can see from below, the total should be -$109,081.03, not the -$244,465.40.  Please advise.   Thanks.  

[amitchandak]

@rwong1 , this can happen when interactions are off , you are using all of allselected in formula. Or some row context is used

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

[rwong1]

I'll try to remove sensitive data.  I just want to know in general if you only want to add rows that are visible in a table (in this case, it's the top table), how do you do that?  What's the formula?  There should be a simply way to use SUMX correct?  I know why it's not adding up and it's because I EXCLUDED other transactions that are also coded PBL and is hidden from the first table.  What is the solution and is SUMX correct?

Thank you.

[v-janeyg-msft]

Hi, @rwong1 

You should know that the context of the measure in the card is completely different from the context of the table. As for dax, it is flexible, and the value of sumx is also different according to different contexts. So, just providing a screenshot does not help solve the problem. You need to calculate the sum of these two values in the meausre of the card to get the correct result.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[rwong1]

Is there a generic formula to sum ONLY the visible rows in table 1?  Even if it is a calculated column, I'm fine with that.  The problem now is Power Bi is reading all transactions amounts that are hidden that have the code PBL plus the ones that are visible in the first table. I don't want that.  I don't have any problems with the second table.  It's only the first one.

Thanks.

[v-janeyg-msft]

Hi, @rwong1 

There is no general formula. The card visual has its own separate context and can't get the sum of the visible rows in the table visual. You need to use the summarize function in the measure of the card to construct the same environment to get the same result. 

If you can understand me and still need help, please feel free to ask me.

Best Regards

Janey Guo

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

[rwong1]

Ok can you guide me through this?  I'm fairly new to Power Bi.  How do I construct the same environment?

[v-janeyg-msft]

Hi, @rwong1 

You need to use the summraize function to build a virtual table, and then calculate the sum in the virtual table.

Like: sumx(summraize(table,"a",sumx()),[a]),The specific steps need to be filled in according to the data and context.

If you need more practical help, Can you share some sample data on onedrive for business and your desired result?

Reference:SUMMARIZE function (DAX) - DAX | Microsoft Docs

Best Regards

Janey Guo

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

[rwong1]

Hi I took a look.  I understand we have to use sum functions.  However, what I need is to sum only rows visible in the first table.  That's where I'm stuck.  It's not a simple sum function.  Whatever I filtered/excluded, I don't want PowerBi to include those in the sum calculation.  I have transactions with the same prospect code that I purposely excluded from the table.  How do I just SUM rows visible in the table?

[v-janeyg-msft]

Hi, @rwong1 

I have already said what can be described in words. If you still can’t understand, I suggest you provide some sample data and your desired result, otherwise it will be difficult for us to help you,thanks!

Best Regards

Janey Guo

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