Double filter to calculate revenue

[ljimenezvega84]

Hi everyone, i have as i believe a simple question.

I have a ddbb with a small structure (only this columns: account(revenue and expenses), type (there are 3 types, actual, plan and last year), and ammount $). I need to create a calculate field who gave me the account (revenue) but only when the type is actual.  the idea is multiply that new calculated field *-1 because its a negative value in the ddbb. There is possible to do this?

Thanks in advance

[audreygerred · Accepted solution]

Hi! I created some dummy data to demonstrate what I did:

With this data I then created the following measure:

TotalRevenueActual :=
CALCULATE(
SUM('YourTableName'[Amount]),
'YourTableName'[Account] = "Revenue",
'YourTableName'[Type] = "Actual"
)

The results: 

And, -100 is accurate as it is the amount where Account is Revenue and Type is Actual. Hope this helps!

Another option would be in Power Query - you can pivot the data so that Revenue and Expense are two different columns. 

[ljimenezvega84]

Hi Audreygerred, thanks a lot. it probably will work!

Regards

[audreygerred]

No problem! Happy to help! Also, I forgot to mention the handling of the negative. I adjusted the DAX below:

TotalRevenueActual =

CALCULATE(

    SUM('Table (7)'[Amount]),

    'Table (7)'[Account] = "Revenue",

    'Table (7)'[Type] = "Actual"

)*-1