topic Please can someone tell me what's wrong with my formula? in DAX Commands and Tips

Please can someone tell me what's wrong with my formula?

MW_TSC — Tue, 02 Jul 2024 14:37:15 GMT

Hi! Newbie here 🙂

So, I've spent a couple of hours on the forums trying to work out how to display one sum on a card, of two different totals displayed on two other visuals. To get the totals, I have had to filter the same dataset two different ways.

These are the two calculations:

1) Sum of Reverse Vat Payment Amount, where the Date Paid is Blank.

2) Sum of Invoice Totals, where the Date Paid is Blank and the Reverse VAT? is No.

I have both of these totals displayed in two different visual tables but I would like to add them up and show them together on a card and I cannot for the life of me work out how to do it!

Any help would be appreciated 🙂

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Tue, 02 Jul 2024 14:37:40 GMT

updated with screenshot of formula 🙂

Re: Please can someone tell me what's wrong with my formula?

Idrissshatila — Tue, 02 Jul 2024 14:38:03 GMT

Hello MW_TSC ,

if you have them as two measures, then you create a measure as the following

total = measure1 + measure2

Re: Please can someone tell me what's wrong with my formula?

Gabry — Tue, 02 Jul 2024 14:39:23 GMT

Hi MW

I don't understand what's your issue. Did you already made those 2 measures?

You add a multi card visual on the page and then drag and drop the 2 measures inside of it, just that.

Or were you asking something different?

Maybe your issue is that the card can take just one measure, but you can use the new card that can take more than one, or use the detail in the format pane. Please detail better your issue.

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Tue, 02 Jul 2024 14:39:57 GMT

Hi! I don't have them as measures, I've just filtered my two visual tables accordingly

Re: Please can someone tell me what's wrong with my formula?

Idrissshatila — Tue, 02 Jul 2024 15:56:19 GMT

Hello MW_TSC ,

can you paste the measure here please, not as a ascreenshot.

Re: Please can someone tell me what's wrong with my formula?

Anonymous — Wed, 03 Jul 2024 05:27:18 GMT

Hi MW_TSC ,

Thanks for the reply from Idrissshatila and Gabry , please allow me to provide another insight:

1. based on your description, i created these data.

2. create MEASURE.

Sum Amount = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date paid] <> BLANK()))

Sum Total = CALCULATE(SUM('Table'[total]),FILTER(ALL('Table'),'Table'[date paid] <> BLANK() && 'Table'[reverse vat] <> "No"))

All = [Sum Amount] + [Sum Total]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Wed, 03 Jul 2024 08:59:49 GMT

Hi, thankyou for your reply.

My data does not match what you have pasted above, so please allow me to explain.

Firstly, all of the lines of data have an invoice total, whether or not they are Reverse VAT. If they are not Reverse VAT, the invoice subtotal applies. If they are reverse VAT, the reverse VAT payment amount applies and the invoice subtotal does not.

Therefore, I created two visual tables in Power BI as as representation of my excel data. Each visual table is filtered based on the above criteria, as follows:

Here is a snapshot of my excel data:

I am trying to create a card to display the sum of the two tables.

The error message I am getting is the following: "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

My formula is as follows - I am complete newbie so I am not sure what I am doing wrong 🙂

Measure = CALCULATE(

sum('Invoice Trackers'[Reverse VAT Payment amount (£)]), FILTER('Invoice Trackers', [Date Paid])<>BLANK()

SUM('Invoice Trackers'[Invoice Total (£)]), FILTER('Invoice Trackers', [Date Paid])<>BLANK(), FILTER('Invoice Trackers', [Reverse VAT?])<>"No")

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Wed, 03 Jul 2024 09:01:20 GMT

Hi Gabry! Thankyou so much for your reply. I have (hopefully) better explained this in a response to v-kaiyue-msft, if you are please able to check that out?

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Wed, 03 Jul 2024 09:03:11 GMT

Hi Idrisshatlia! Thankyou so much for your reply!

My formula is a follows:

Measure = CALCULATE(

sum('Invoice Trackers'[Reverse VAT Payment amount (£)]), FILTER('Invoice Trackers', [Date Paid])<>BLANK()

SUM('Invoice Trackers'[Invoice Total (£)]), FILTER('Invoice Trackers', [Date Paid])<>BLANK(), FILTER('Invoice Trackers', [Reverse VAT?])<>"No")

I have also pasted a more in depth reply In response to v-kaiyue-msft, if you can please check this out?

Re: Please can someone tell me what's wrong with my formula?

MW_TSC — Wed, 03 Jul 2024 13:52:12 GMT

Hi everyone,

Just to let you all know that I've worked it out myself, I had to create 3 seperate measures then a 4th one that summarised them all, all sorted now 🙂