Dax calculation to sum based on criteria

jprlimey · October 12, 2020, 6:40pm

I thought at first this would be easy to figure out, but alas I’m stumped.

I have the following table

Contract Review-1

Where i want to sum up all the values in the Hrs to Complete column, when the Step Number Text column is “Lvl4 Approval”, column WF Status is “Completed” and column Step Status = “Approved”. The twist is that i want to sum up all the Review No rows where the above is true.

For example Contract No 17778, i would want all the rows for 17778 that match the above criteria.

I tried the following measures unsuccessfully.

Version 3 sum = 
CALCULATE(
    SUMX(
        FILTER(
            ALLEXCEPT('Contract Review', 'Contract Review'[Review No]),
            'Contract Review'[Step Number Text] = "Lvl4 Approval"
            && 'Contract Review'[ Step Status] = "Approved"
            &&'Contract Review'[WF Status] =  "Completed"),
            [Hrs to Complete All]
    )
)

Version 4 sum = 
    CALCULATE(
        SUM('Contract Review'[Hrs to complete]),
            FILTER(
                ALL('Contract Review'),
                (
                'Contract Review'[Review No]) = MAX('Contract Review'[Review No]) &&
                'Contract Review'[Step Number Text] = "Lvl4 Approval"
                && 'Contract Review'[ Step Status] = "Approved"
                &&'Contract Review'[WF Status] =  "Completed")
        )


Hrs to Complete All = 
    CALCULATE(
        [Hrs to Complete],
            USERELATIONSHIP('Date'[Date],'Contract Review'[Assign Date])
    )

My actual table looks like this, with the measures

Contract Review-2

If we were to sum all rows for contract review 17778, then the actual result would be

Contract Review-3

In each case my measures are only returning the value in the row where column “Step Number Text” = “Levl4 Approval”

What do i need to do to capture all rows of Contract Review when contract Number is 17778

Thanks
J

EnterpriseDNA · October 13, 2020, 1:20am

Thanks for posting your question @jprlimey. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Anurag · October 13, 2020, 5:42am

Hi,

Can you provide the sample pbix so i can play with data and try to solve your problem .

thanks

kentyler · October 13, 2020, 8:54am

jprlimey:

  CALCULATE(
        SUM('Contract Review'[Hrs to complete]),
            FILTER(
                ALL('Contract Review'),
                (
                'Contract Review'[Review No]) = MAX('Contract Review'[Review No]) &&
                'Contract Review'[Step Number Text] = "Lvl4 Approval"
                && 'Contract Review'[ Step Status] = "Approved"
                &&'Contract Review'[WF Status] =  "Completed")
        )

try
VAR ReviewNo = MAX(‘Contract Review’[Review No])
CALCULATE(
SUM(‘Contract Review’[Hrs to complete]),

            ALL('Contract Review'),
            
            'Contract Review'[Review No]) = ReviewNo,
            'Contract Review'[Step Number Text] = "Lvl4 Approval",
            'Contract Review'[ Step Status] = "Approved",
            'Contract Review'[WF Status] =  "Completed")
    )

jprlimey · October 13, 2020, 7:14pm

kentyler,

thanks for the dax suggestion.

i had to re-write, specifically i had to add the RETURN after the VAR declaration. Unfortunately it returned the same result as my Dax formula. Any other suggestion. Your like mine only returned the sum of the row highlighted below

it should have returned the sum for all the following

Contract Review-5

Version 7 sum = 
VAR ReviewNo = MAX('Contract Review'[Review No])
RETURN

CALCULATE(
    SUM('Contract Review'[Hrs to complete]),
    ALL('Contract Review'),
        'Contract Review'[Review No] = ReviewNo,
        'Contract Review'[ Step Status] = "Approved",
        'Contract Review'[Step Number Text] = "Lvl4 Approval",
        'Contract Review'[WF Status] = "Completed")

Thanks
J

BrianJ · October 13, 2020, 9:24pm

@jprlimey,

Based on my understanding of the question, I believe the measure below works:

Version 5 sum = 
    
VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    'Contract Review'[Step Status] = "Approved"
                ),
                'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )

VAR PrelimBinary =
IF( Prelim > 0, 1, 0)

VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hours to Complete] * PrelimBinary
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)

RETURN
Result

I hope this is helpful. Full solution file posted below.

– Brian

eDNA Forum - ALLEXCEPT SUM Solution.pbix (22.0 KB)

jprlimey · October 13, 2020, 10:59pm

Brian,

That is some Dax formula. Yes it worked but now to try to understand the logic. I’m impressed!

Below is the outputted table

Contract Review-6

And the Dax measure

Version 8 sum = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    'Contract Review'[ Step Status] = "Approved"
                ),
                'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )
VAR PrelimBinary =
IF( Prelim > 0, 1, 0)
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result

BrianJ · October 13, 2020, 11:32pm

@jprlimey,

It’s a fair amount of DAX, but the logic is not as complex as it might appear:

Prelim - count up the number of records within a contract review number that meet all three of your conditions

PrelimBinary - if ANY records within a given contract review # meet all those conditions, assign a 1, otherwise assign a 0,

Result - by contract review #, sum the product of (Hrs to Complete * 0/1 PrelimBinary variable) - this effectively sums all the records within a given contract review # where at least one record met the criteria, and zeroes out all the other sums where no records within the contract review #` met all the criteria

The key to this measure is aggregating at the contract review # level, using the ALLEXCEPT function.

https://info.enterprisedna.co/dax-function-guide/allexcept/

I hope this is helpful.

Brian

jprlimey · October 14, 2020, 12:34am

Yes I did notice that the measure returned zero for all contract numbers that didn’t match the criteria. To remove them from the visual I just filtered on values greater than 0, I wonder if adding an if statement inside the Result Variable to check if value > 0, sum those but return BLANK() if = to 0

Ultimately I will be counting the number of contract reviews, as there are numerous types each with different criteria. So your Dax will aid greatly in developing the additional measures. The measures will enable determining the distribution of contract review types and the cycle time to complete each along with average or mean time to complete.

I was prompted or should I say directed to develop an SPC chart (I-MR) to show statistically how we are performing

This forum is absolutely fabulous, it’s amazing what you can learn to broaden your Power BI experience and expertise.

Thanks again to all
J

BrianJ · October 14, 2020, 2:17am

@jprlimey,

To make life simpler, and avoid having to put the additional visual filter on, just change the PrelimBinary variable in the measure above to this:

VAR PrelimBinary =
IF( Prelim > 0, 1, BLANK())

That will give you the exact result you’re looking for:

Revised solution file attached below.

Brian
eDNA Forum - ALLEXCEPT SUM Solution.pbix (21.9 KB)

P.S. totally agree with you about the forum – it’s an amazing community.

jprlimey · October 14, 2020, 1:29pm

Brian,

So I’m progressing nicely with my report. But alas i now want to separate results for contract reviews that are not level 2, 3 or 4, these would be classed as level 5.

For level 5 reviews no ‘Contract Review’[Step Number Text] exists detailing they are level 5.
Per the table below you can see that Level 2 thru 4 step status is “Skipped” and the value for Hrs to Complete is #VALUE (none exists).

Contract Review-7

I re-wrote to DAX with and OR statement thinking that would capture when Level 2 thru 4 were status of “Skipped”, the DAX is below

Level 5 Total = 
 VAR Prelim5 = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    OR(
                        OR(
                            OR(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    ),
                    'Contract Review'[Step Number Text] = "Lvl3 Approval",
                    ),
                    'Contract Review'[Step Number Text] = "Lvl2 Approval",
                    ),
                    'Contract Review'[ Step Status] = "Skipped"
                 ),
                  'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )
VAR PrelimBinary5 =
IF( Prelim5 > 0, BLANK(), 1)
VAR Result5 =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary5
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result5

I’m getting an error - Argument’2’ in OR function is required.

Any thoughts on the direction to go?

Thanks
J

BrianJ · October 14, 2020, 1:50pm

@jprlimey,

Ouch - that nested AND/OR makes my brain hurt.

I would suggest breaking it down into a few separate variables that each use SWITCH or SWITCH(TRUE()). This will dramatically simplify the filter condition in your CALCULATE statement and make it easier both to understand and debug.

Give it a go, and shout if you run into problems.

Brian

jprlimey · October 14, 2020, 9:26pm

All,

As Brian has done a spectacular job in getting this far, i’d like to reach out to anyone else who would like to contribute.

As Brian suggested i tried several versions of DAX to utilize the SWITCH function to overcome the nested AND OR statements, below are 2 that i had most success with, although neither is correct.

Level 5 Test 1 = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
             AND(
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "Lvl4 Approval" && 
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0,                              
                    'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 
                    'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 1
                ), 
                    'Contract Review'[WF Status] =  "Completed" 
            ) = TRUE
        )
)
VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary8
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result


Level 5 Test 2 = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            
            AND(
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "Lvl4 Approval" && 
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0,                              
                    'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 
                    'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 0
                ), 
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "End" &&
                    'Contract Review'[WF Status] =  "Completed", 1,
                    'Contract Review'[Step Number Text] = "Notify CSR Approved" &&
                    'Contract Review'[Step Number Text] = "End", 1, 1)
            ) = TRUE
        )
)

VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary9
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result

In each DAX what i believe i need to do is somehow get the VAR Prelim to be > than 1. as we loop through all the contracts. Where the conditions are TRUE for Level 2 thru 4 “Step Number Text” and the associated “Step Status” is “Skipped”, i assign a 0 value in the SWITCH statement. As the IF statement then checks to see if > 1, and assigns a value of 1 for true and BLANK() for false.

Then for each contract review with the SUMX function we intend to gather the matching values when not a Level 2 thru 4 contract review.

Brian was most helpful in supporting me in getting the correct DAX for contract reviews that were Level 2 thru 4, the last remaining piece is getting the elusive Level 5 number.

The table below shows the results from the DAX Level 5 Test

As you can see contract review no 17778 is in the table. This is a Level 4 contract review and should not be part of the Level 5 table, hence i know i have issues with the DAX.

Contract Review-9

I have also attached a small sample of the database in an Excel format, perhaps this could be uploaded into the pbix file that Brian created, to aid in solving this issue.

Sample Contract Review.xlsx (10.7 KB)

Thanks
J

BrianJ · October 14, 2020, 9:44pm

@jprlimey,

Very considerate, but I’m happy to see this one through to the end with you. I’ve got a big PBI report due tomorrow that takes forever to refresh, so this will give me something productive to do instead of impatiently watching Power Query grind through my Dynamics data over a slow Odata connection…

Brian

jprlimey · October 14, 2020, 11:22pm

Brian,

Thank you so much for wanting to push this through to the end. As being a relative newcomer to EnterpriseDNA, I was just tying to respond to the forum prompts when writing my last message, where it encouraged me to get other involved.

Thanks again
J

BrianJ · October 15, 2020, 1:49am

@jprlimey

No, that’s exactly the right way to handle it. This one has just kind of got me hooked, and I’m enjoying working with you on it.

This iteration was a bit more difficult than the last one, but I think I’ve got it working properly now. Please check to make sure I interpreted the level 5 conditions correctly.


FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Rev2'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Rev2'[ Step Status] ) = "Skipped"
) 

SecondScreen = IF(
    SELECTEDVALUE( 'Contract Rev2'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE)

Level5 Total =
VAR Prelim = CALCULATE(
    COUNTROWS( 'Contract Rev2' ),
    FILTER(
      'Contract Rev2',
        [FirstScreen] = TRUE &&
        [SecondScreen] = TRUE
    ),
    ALLEXCEPT(
      'Contract Rev2',
      'Contract Rev2'[Review No]
    )
) 
VAR AllExTot = CALCULATE(
    SUMX( 'Contract Rev2', [Total Hours2] ),
    ALLEXCEPT(
      'Contract Rev2',
      'Contract Rev2'[Review No]
    )
) 
VAR Result = IF( Prelim = 3, AllExTot, BLANK() ) 

RETURN
Result

I hope this is helpful. Full solution file attached below

Please give a shout if you have questions (note: SWITCH also would’ve worked fine for the variables, but in looking at the problem in more detail I found a more efficient construct using IN)

Brian
eDNA Forum - Level 5 SUM Solution.pbix (32.1 KB)

jprlimey · October 15, 2020, 1:00pm

Brian,

I use your DAX and it didn’t work, yes I’m majorly disappointed, as as you most likely, i thought you had it based on your pbix file

I create the separate measures First Screen and Second Screen, just the same as you did in the attached pbix file, below are the measures

First Screen = 
//VAR FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Review'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Review'[ Step Status] ) = "Skipped"
) 

Second Screen = 
//VAR SecondScreen = 
IF(
    SELECTEDVALUE( 'Contract Review'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE
)

The table outputs are below for each measure

Contract Review-10

Everything up to this point i believe looks good.

I then added the Level5 Total measure, per below.

Level5 Total = 

VAR FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Review'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Review'[ Step Status] ) = "Skipped"
) 

VAR SecondScreen = 
IF(
    SELECTEDVALUE( 'Contract Review'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE
) 

VAR Prelim = 
CALCULATE(
    COUNTROWS( 'Contract Review' ),
    FILTER(
      'Contract Review',
        [First Screen] = TRUE &&
        [Second Screen] = TRUE
    ),
    ALLEXCEPT(
      'Contract Review',
      'Contract Review'[Review No]
    )
) 

VAR AllExTot = CALCULATE(
    SUMX( 'Contract Review',
        [Hrs to Complete All]
    ),
    ALLEXCEPT(
      'Contract Review',
      'Contract Review'[Review No]
    )
) 

VAR Result = IF( Prelim = 3, AllExTot, BLANK() ) 
RETURN
Result

I noticed that the logic for VAR FirstScreen and SecondScreen, were identical to your measuses First Screen and Second Screen in your pbix file, and under the VAR Prelim you reference these actual measures to test if true. So i commented out the FirstScreen and SecondScreen VAr as i felt they were redundant. It still didn’t provide the anything for the table, see below.

Contract Review-11

In the table i’m also showing the measure Hrs to Complete All, i just wanted to make sure it was still returning values. I also added the screen measures and as you see Level5 total is blank to all contract reviews.

The Hrs to Complete All measure is below.

Hrs to Complete All = 
    CALCULATE(
        [Hrs to Complete],
            USERELATIONSHIP('Date'[Date],'Contract Review'[Assign Date])
    )

and the Hrs to Complete measure is below.

Hrs to Complete = SUM (‘Contract Review’[Hrs to complete])

Any idea what may be the issue?

Thaknks
J

BrianJ · October 15, 2020, 1:11pm

@jprlimey,

Sorry - originally I did firstscreen and secondscreen as variables, then in the debugging branched them out as separate measures, but didn’t take the variables out of the Level 5 measure, which I should have to avoid confusion.

I’m currently crunching on a major work deadline. If it’s okay, will take a look back at the solution this evening and see what needs to be done to revise it.

Bummer – thought I had it on the first shot, but will definitely follow up soon to get you what you need.

Brian

jprlimey · October 15, 2020, 1:14pm

Brian,

Thank you so much, this is almost as good as one of the Power Bi Challenges… ha ha!

J

BrianJ · October 16, 2020, 10:28pm

@jprlimey,

Okay, I think I finally measure branched this one into submission. Here’s the basic logic:

Created a Both Screens measure, assigning a 1 if First Screen and Second Screen were both true, 0 otherwise
Totaled the Both Screens measure by Contract Review Number
If the total computed above in #2 = 3 (meaning levels 2, 3 and 4 were all skipped), then computed the sum of hours by Contract Review Number, otherwise assigned a blank

Here is all put together. Full solution file posted below.

Please let me know if this gets you what you need – I hope it does.

– Brian

eDNA Forum - Level 5 SUM Solution Rev2.pbix (32.9 KB)