cancel
Showing results for
Did you mean:
swilcox Frequent Visitor

VALUES function - adding extra condition

Hi There,

I'm very new to Power BI and DAX.  I currently have this DAX

GrossWithCosts (Station Net) = SUMX(
VALUES('Data'[StationShortName])
,
var thisProduct = 'Data'[StationShortName]
var thisFactor = IF(thisProduct = "Station1" , 0.33 , 1 )
return
CALCULATE(SUM('Data'[GrossWithCosts]) * thisFactor
)
)

which correctly returns a NET value of 33% of the revenue IF Station1 has revenue.
How do I add an additinal Station into this DAX with a different %.
eg: IF Station2 then multiple by 0.2

End result being 1 NET column with different stations filtered by different %'s.
thanks
Shannon
1 ACCEPTED SOLUTION

Accepted Solutions Super User

Re: VALUES function - adding extra condition

Try with following

GrossWithCosts (Station Net) =
SUMX (
VALUES ( 'Data'[StationShortName] ),
VAR thisProduct = 'Data'[StationShortName]
VAR thisFactor =
SWITCH (
TRUE (),
thisProduct = "Station1", 0.33,
thisProduct = "Station2", 0.2,
1
)
RETURN
CALCULATE ( SUM ( 'Data'[GrossWithCosts] ) * thisFactor )
)
2 REPLIES 2 Super User

Re: VALUES function - adding extra condition

Try with following

GrossWithCosts (Station Net) =
SUMX (
VALUES ( 'Data'[StationShortName] ),
VAR thisProduct = 'Data'[StationShortName]
VAR thisFactor =
SWITCH (
TRUE (),
thisProduct = "Station1", 0.33,
thisProduct = "Station2", 0.2,
1
)
RETURN
CALCULATE ( SUM ( 'Data'[GrossWithCosts] ) * thisFactor )
)
swilcox Frequent Visitor

Re: VALUES function - adding extra condition

I have a further factor to add to this DAX.

Station1 remains as below.

I also need to include IF Year = 2019 or greater then Station 2 is multiplied by 0.2

AND if year = 2018 then Station2 is multiplied by 0.18

AND if year is 2017 or less then Station2 is multipled by 0.4

my year field is in a seperate Calendar table

is this possible?

Announcements Top Kudoed Authors
Users Online
Currently online: 310 members 3,102 guests
Recent signins:
• shrimankabra • Sandy
• poweringbi123 • NKhan • AndrewSorokin • SimenOmmundsen • morz3d • VanessaMachuca • testpbiprofile • walter • jorgemur • kitterific • Grzesiek89 • GVDD 