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

[MW_TSC]

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 🙂

[v-kaiyue-msft]

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.

[MW_TSC · Accepted solution]

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 🙂

[MW_TSC]

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")

[Gabry]

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.

[MW_TSC]

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?

[Idrissshatila]

Hello @MW_TSC ,

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

total = measure1 + measure2

[MW_TSC]

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

[MW_TSC]

updated with screenshot of formula 🙂

[Idrissshatila]

Hello @MW_TSC ,

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

[MW_TSC]

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?