Forum Discussion
Conveting excel formula into DAX
Hi,
I have a following excel formula and I have to convert that into DAX query. need solution
=SUMIFS('Raw Data'!$F:$F,' Raw Data'!$A:$A,$A$4,'Raw Data'!$E:$E,$B6,' Raw Data'!$B:$B,$A6,' Raw Data'!$C:$C,">="&C$1,'Raw Data'!$C:$C,"<"&C$2)
6 Replies
- ValtteriN
Community Champion
Hi,
You can convert this into DAX by using CALCULATE + SUM.
Example:
Here we are calculating SUM IF cakes = T1 or T2Cake Total = CALCULATE(SUM(Cakes[Sums]),'Cakes'[Cakes]="T1" || 'Cakes'[Cakes]="T2")
End result is 500 as expected.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/I just need explanation for the excel expression?
What does excel expression represents?
If i break the above expression
=SUMIFS('Raw Data'!$F:$F,- means sheet name rawdata we have to check F column in excel' Raw Data'!$A:$A,$A$4,- means we have to check from columA to column A to 4th row of column A
'Raw Data'!$E:$E,$B6,- means we have to check column E to B column 6th row' Raw Data'!$B:$B,$A6,'-means column B to A6
' Raw Data'!$C:$C,">="&C$1,- means column C should be >=Column C first row ,' Raw Data'!$C:$C,"<"&C$2)- means Column C should be < Column C 2nd row?
FreemanZSuper User
hi skaul
converting to DAX, it looks like:
Value =CALCULATE(SUM(TableName[HeaderColumnF]),TableName[HeaderColumnA] = "Content in A4",TableName[HeaderColumnE] = "Content in B6",TableName[HeaderColumnB] = "Content in A6",TableName[HeaderColumnC] >= "Content in C1",TableName[HeaderColumnC] < "Content in C2")if there is any issue, please consider providing some sample data and @me.
Hi,
I am forwarding the sample data with expression for which I am required to convert excel expression to dax
EXCEL EXPRESSION
=SUMIFS(' Raw Data'!$F:$F,' Raw Data'!$A:$A,$A$4,' Raw Data'!$E:$E,$B6,' Raw Data'!$B:$B,$A6,' Raw Data'!$C:$C,">="&C$1,' Raw Data'!$C:$C,"<"&C$2)
Sample data is present below
Name Alias Period Source Unit Unit Value Form Type Burgandy Complex Consumed units 1/1/2020 M CO2 tonnes 17818.62 Actual Burgandy Complex Consumed units 1/2/2020 M CO2 tonnes 10310.76 Actual Burgandy Complex Consumed units 1/3/2020 M CO2 tonnes 19668.96 Actual Burgandy Complex Consumed units 1/4/2020 M CO2 tonnes 18229.06 Actual Burgandy Complex Consumed units 1/5/2020 M CO2 tonnes 20039.96 Actual Burgandy Complex Consumed units 1/6/2020 M CO2 tonnes 18712.27 Actual Burgandy Complex Consumed units 1/7/2020 M CO2 tonnes 17036.43 Actual Burgandy Complex Consumed units 1/8/2020 M CO2 tonnes 18402.42 Actual Burgandy Complex Consumed units 1/9/2020 M CO2 tonnes 16682.36 Actual Burgandy Complex Consumed units 1/10/2020 M CO2 tonnes 16128.51 Actual Burgandy Complex Consumed units 1/11/2020 M CO2 tonnes 18836.17 Actual Burgandy Complex Consumed units 1/12/2020 M CO2 tonnes 11010.48 Actual Burgandy Complex Consumed units 1/13/2020 M CO2 tonnes 18082.79 Actual Burgandy Complex Consumed units 1/14/2020 M CO2 tonnes 16181.71 Actual Burgandy Complex Consumed units 1/15/2020 M CO2 tonnes 15752.86 Actual Burgandy Complex Consumed units 1/16/2020 M CO2 tonnes 17887.4 Actual Burgandy Complex Consumed units 1/17/2020 M CO2 tonnes 20906.05 Actual Burgandy Complex Consumed units 1/18/2020 M CO2 tonnes 18855.13 Forecast Burgandy Complex Consumed units 1/19/2020 M CO2 tonnes 18855.13 Forecast Burgandy Complex Consumed units 1/20/2020 M CO2 tonnes 18655.13 Forecast Burgandy Complex Consumed units 1/21/2020 M CO2 tonnes 18555.13 Forecast Burgandy Complex Consumed units 1/22/2020 M CO2 tonnes 18355.13 Forecast - FreemanZ
hi skaul
try this:
Value =CALCULATE(SUM('Raw Data'[Value]),'Raw Data'[Name] = "Content in A4 of your Excel", //replace the content inside ""'Raw Data'[Unit] = "Content in B6",'Raw Data'[Alias] = "Content in A6",'Raw Data'[Period] >= "Content in C1",'Raw Data'[Period] < "Content in C2")
