Measure: Calculate % of results that equal criteria within another table

[Anonymous]

Hi,

I need help creating a custom measure that will return the % of [Claim ID] that have a [Days Between] value equal to or less than the reference values in another table.  This percentage would need to calculate the percentage based on only that [Company] number of distinct [Claim ID].  I need the measure to by dynamic so that it calculates based on a Slicer's selection for that [Company]

Here is an example data set with the Claim Data:

Here is an example data set with the reference values:

Given this example data, I would expect the measure to return a value of 50% when USA is selected from the slicer and 67% when Korea is selected. 

- USA should = 50% since 3 out of 6 distinct [Claim ID] had a [Days Between] value equal to or less than 30

- Korea should = 67% since 2 out of 3 distinct [Claim ID] had a [Days Between] value equal to or less than 20

Can you please assist me in creating this type of measure?

Thank you,

Andrew

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try this...

% Claims Better Than Goal = 
VAR ClaimSummary =
    ADDCOLUMNS(
        Claims,
        "DaysBetween",
        DATEDIFF(
            Claims[StartDate],
            Claims[EndDate],
            DAY
        ),
        "Goal",
        RELATED(Companies[Goal])
    )
VAR ClaimCount = COUNTROWS(ClaimSummary)
VAR ClaimsBeatingGoalCount =
    COUNTROWS(
        FILTER(
            ClaimSummary,
            [DaysBetween] <= [Goal]
        )
    )
RETURN

DIVIDE(
    ClaimsBeatingGoalCount,
    ClaimCount,
    BLANK()
)

Hope this helps!

[PijushRoy · Accepted solution]

Hi, @Anonymous 

Create a calculated col in datatable

Goal = LOOKUPVALUE(RefrenceTable[Goal],RefrenceTable[Company],'DataTable'[Company])

Then use this formula

Measure = VAR _DistinctCountClaimID = CALCULATE(DISTINCTCOUNT('DataTable'[Claim ID]),ALLEXCEPT('DataTable','DataTable'[Company]))

VAR _days = SELECTEDVALUE('DataTable'[Goal])

VAR _dayscount = CALCULATE(COUNT('DataTable'[Company]), 'DataTable'[Days Between] <= _days)

RETURN

_dayscount/_DistinctCountClaimID

Download pbix

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[PijushRoy · Accepted solution]

Hi, @Anonymous 

Create a calculated col in datatable

Goal = LOOKUPVALUE(RefrenceTable[Goal],RefrenceTable[Company],'DataTable'[Company])

Then use this formula

Measure = VAR _DistinctCountClaimID = CALCULATE(DISTINCTCOUNT('DataTable'[Claim ID]),ALLEXCEPT('DataTable','DataTable'[Company]))

VAR _days = SELECTEDVALUE('DataTable'[Goal])

VAR _dayscount = CALCULATE(COUNT('DataTable'[Company]), 'DataTable'[Days Between] <= _days)

RETURN

_dayscount/_DistinctCountClaimID

Download pbix

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try this...

% Claims Better Than Goal = 
VAR ClaimSummary =
    ADDCOLUMNS(
        Claims,
        "DaysBetween",
        DATEDIFF(
            Claims[StartDate],
            Claims[EndDate],
            DAY
        ),
        "Goal",
        RELATED(Companies[Goal])
    )
VAR ClaimCount = COUNTROWS(ClaimSummary)
VAR ClaimsBeatingGoalCount =
    COUNTROWS(
        FILTER(
            ClaimSummary,
            [DaysBetween] <= [Goal]
        )
    )
RETURN

DIVIDE(
    ClaimsBeatingGoalCount,
    ClaimCount,
    BLANK()
)

Hope this helps!

[Anonymous]

I tried this but the 

RELATED(Companies[Goal]) portion of the DAX gave me trouble.  My reference table has a numerical value set while the DATEDIFF is returning a interval of days.  So, the DAX isn't finding a similar data type.

How can I resolve this issue?

[littlemojopuppy]

@Anonymous DATEDIFF returns the count of interval boundaries crossed between two dates: a numeric result.  It clearly works...

Could you share your pbix to try to figure this out?

[Anonymous]

I verified in Power Query that [Adjudicated Days] is set as a numerical value, table name = '

Adjudicated_PG_30'

Here is a screen grab of the DAX.  The RELATED ( Adjudicated_PG_30[Adjudicated Days] ) returns an error of "Parameter is not correct type" and it then cannot find the column name "Adjudicated Days"

[littlemojopuppy]

@Anonymous you're trying to solve this in Power Query???  😯

[Anonymous]

Oh no.  I was just showing you that I confirmed in Power Query that 

Adjudicated_PG_30[Adjudicated Days] is a numerical value.

[littlemojopuppy · Accepted solution]

@Anonymous if you change the data type from a duration to whole number you should be ok.  Worked with the pbix I attached.