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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MattAnalytics
New Member

Calculate net of VAT and un GBP with DAX

Dear community,

 

I am looking for some help to write a DAX formula that would return the sales amount net of VAT and in GBP based on two columns. 

 

My current data model is composed of the following tables:

Actual { Property, Date, Currency, Food Sales, Beverage Sales}

VAT { Date, VAT, Item }                                              < Items = {Food Sales, Beverage Sales}

Currency  { Currency, Rate, Date }                             < Currency = { EUR/GBP }

Calendar { Date, Month ... etc }                                 < As Date Table

BusinessUnits { Property Name, Code, etc.... }

 

Note that the VAT and currency have different values over time.

 

I have the following relationships: 

Actual.Property 1 to many relationships with BusinessUnits.PropertyName

Actual.Date 1 to many relationships with Calendar.Date

VAT.Date 1 to many relationships with Calendar.Date

Currency.Date 1 to many relationships with Calendar.Date

 

This is my current formula that calculates my Net Food Sales. I'm not sure how to write the second part that would transform my EUR revenues into GBP. I'm wondering if I should use the VAR function, any recommendations?

 

Net Food Sales:=SUMX( Actual,

DIVIDE( Actual[Food Sales] ,

(1 + IFERROR(

LOOKUPVALUE(VAT[VAT],

VAT[Item] , "Food Sales", VAT[Date], Actual[Date]) , 0.2 ))

))

 

Also, would you recommend computing these calculations in PowerQuery or keep them in DAX?

Many thanks for your help.

Best,

 

1 REPLY 1
dedelman_clng
Community Champion
Community Champion

Hi @MattAnalytics -

 

I can give you some generic pointers on DAX, but without a copy of your data/model/pbix it will be hard to give specific code. If you can share a copy of your pbix with sensitive data removed, we can get to specifics.

 

That being said, let me answer a couple of your questions:

 


I'm wondering if I should use the VAR function, any recommendations?

 


Assuming you mean using variables (VAR ... RETURN), it is a good practice, especially when dealing with lookups to use variables to do the heavy lifting and then at the end you return a simple expression. This also helps with troubleshooting as you can return the value that a variable calculates if the results are not what you expect. In case you're new to variables, syntax for using variables is

 

CalcName = 
VAR __MyVar1 = DAX expression...
VAR __MyVar2 = DAX expression...can use previous variables
RETURN
(usually simplified) DAX expression using variables from above

 

Also, would you recommend computing these calculations in PowerQuery or keep them in DAX?

 


Since it looks like this is a measure and not a calculated column, you would not be able to do this in PowerQuery, and DAX is the answer. Remember that a "column" in a table/matrix visual can be (often is) a measure, so don't confuse the two unless you are looking at a calculation that is specific for each row in the table.

 

Again, if you can share a copy of your PBIX (OneDrive, dropbox, google drive, github, etc), we can work on getting you a specific answer. If you can't, see this article for guidance: How to Get Your Question Answered Quickly 

 

Hope this helps

David

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors