cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swilcox Frequent Visitor
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
Super User

Re: VALUES function - adding extra condition

@swilcox 

 

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
Super User

Re: VALUES function - adding extra condition

@swilcox 

 

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
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?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 310 members 3,102 guests
Please welcome our newest community members: