Multiple Sumifs excel into DAX

Question

I am currently using Power BI as my organization is shifting towards it for the better visuals but they should have mentioned the complexity of the DAX :)

I have some multiple sumifs in an excel file that I cannot under anyway transform into Power BI using DAX

Here is the table I conducted in excel from raw data in another sheet:

and here is the code in the cells:

Red Arrow code:

=IFERROR((SUMIFS(ID_600!$E:$E,ID_600!$D:$D,"sick hours paid",ID_600!$A:$A,K$18)+SUMIFS(ID_600!$E:$E,ID_600!$D:$D,"sick hours unpaid",ID_600!$A:$A,K$18))/SUMIFS(ID_600!$E:$E,ID_600!$D:$D,"regular hours",ID_600!$A:$A,K$18),NA())

• Column A in sheet ID_600 have a date data and it is compared summed if it is equals to 11/5/2018

Blue Arrow code:

=IFERROR((SUMIFS(ID_600!$E$1:$E$5056,ID_600!$D$1:$D$5056,"sick hours paid",ID_600!$A$1:$A$5056,K$18,ID_600!$C$1:$C$5056,$A$23)+SUMIFS(ID_600!$E$1:$E$5056,ID_600!$D$1:$D$5056,"sick hours unpaid",ID_600!$A$1:$A$5056,K$18,ID_600!$C$1:$C$5056,$A$23))/SUMIFS(ID_600!$E$1:$E$5056,ID_600!$D$1:$D$5056,"regular hours",ID_600!$A$1:$A$5056,K$18,ID_600!$C$1:$C$5056,$A$23),NA())

• Column A in sheet ID_600 have a date data and it is compared summed if it is equals to 11/5/2018, in addition to summing if Column "In"(A23) matches the data in Column C in sheet ID_600

And after figuring out the DAX code, I will see what I can do to transform that using visualizations.

Sample file for raw data: https://ufile.io/lfddf

*edited 2

Answer 1

Yes, DAX can be tricky, but in this case the DAX expressions are simpler than the excelformulas. To create something like your pivottable in a matrix visual, create the following three measures:

Total Sick Hours =
CALCULATE ( SUM ( 'table'[hours] ),
    OR ( 'table'[type] = "Sick Hours Paid", 'table'[type] = "Sick Hours Unpaid" ))

Total Regular Hours = CALCULATE( SUM ( 'table'[hours] ), 'table'[type] = "Regular Hours" )

% Sick hours of Regular hours = DIVIDE( [Total Sick Hours], [Total Regular Hours], 0 )

Then create a matrix visual and put dep on Rows, Date on Columns and % Sick hours of Regular hours in Values. Format the % Sick hours of Regular hours as a perecentage (select the measure in the Fields pane and click % on the Modeling tab).

The result should look something like this: