Dax Calculations

Question

I am trying to do some Calculations to figure out "Wage" based on certain Criteria. Here is what I have:SUMX(&nbsp;&nbsp;&nbsp; 'Federal Holidays',&nbsp;&nbsp;&nbsp; SWITCH(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TRUE(),&nbsp;&nbsp;&nbsp; 'Federal Holidays'[Holiday] = "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] = "New Year's Day 3 Days Before", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IFERROR(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Round( [_hours],0) * [_estimatedWageAverage] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[month index] &gt; -16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[month index] &lt; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ,'Federal Holidays'[Holiday] &lt;&gt; "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] &lt;&gt; "New Year's Day 3 Days Before",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IFERROR(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Round( [_hours],0) * [_estimatedWageAverage] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[year index] = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; ))What I need to remove is the SUMX part. I cannot figout another way to do this calculation.&nbsp;What I am looking to do in one Formula, is to do something like this(I did Merge my Federal Holiday table to my Main Table):_estimatedSavingsCurrentYear2 = IF('PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days After" || 'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days Before",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IFERROR(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Round( [_hours],0) * [_estimatedWageAverage] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[month index] &gt; -16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[month index] &lt; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IFERROR(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Round( [_hours],0) * [_estimatedWageAverage] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Dates[year index] = 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] is a Column within the table.&nbsp;&nbsp;

josefprakljacic · Answer

Hey&nbsp;whatlje1&nbsp;,&nbsp;It's no disgrace to publish sample data. Especially if you want help with DAX formulas and the helping persons have to test their formulas themselves in order not to give any wrong answer or to guess blindly.&nbsp;So may I ask you, if you ask for help from others, to also fulfill your contribution and provide sample data?&nbsp;BR,Josef

Forum Discussion

Dax Calculations

1 Reply

Related content

DAX for calculating retroactive values

DAX Calculated Measure

DAX Calculation

Matrix calculation Issue - DAX

DAX Measure for Ranking the valid rows and calculating the average rating