How to add a multiplication to an existing Calculate formula

[JDBOS]

We are a social services organization and need to report demographic information on a quarterly basis.

The following formula is working determine the total number of "Survivors of Domestic Violence" for a filtered period,

but we now need to report on only a percentage of the total supported (Sum of the 'Case Record'[Survivors of DV]).

Here's how it's currently displayed

using:  

   a numeric calculated column

defined as:

Based on the following DAX formula:

Survivors of DV =

COALESCE(CALCULATE(

COUNTROWS('Case Record'),

CONTAINSSTRING('Case Record'[Related_Inquiry__r.Type_s_of_Victimization__c],"Domestic and/or family violence")

),0)

"Case Records" are linked to a "Related Inquiry" that have a "Type of Victimization" field that could have a value of "Domestice and/or family violence"

We've tried adding the multiplication in various places of the formula but the result doesn't change (but PBI does allow the formula's syntax)

Here's an example:

=

COALESCE(CALCULATE(

    COUNTROWS('Case Record'),

    CONTAINSSTRING('Case Record'[Related_Inquiry__r.Type_s_of_Victimization__c],"Domestic and/or family violence")

),0)* (FundingPercent[FundingPercent Value]/100)

Any suggestions would be much appreciated!   And I can scrub data and build a test PBIX but am hoping this is an easy fix 😁

[amitchandak]

@JDBOS , Is grand total wrong is row value wrong ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

One approach

sumx( values('Case Record [clientType])

,

COALESCE(CALCULATE(

COUNTROWS('Case Record'),

CONTAINSSTRING('Case Record'[Related_Inquiry__r.Type_s_of_Victimization__c],"Domestic and/or family violence")

),0)* (FundingPercent[FundingPercent Value]/100))

[JDBOS]

@amitchandak I'll work on a sample PBIX with sensitive info removed

And, to test SUMX, where would it be placed in the sequence of existing nested functions? (within or before the Calculate?)

[amitchandak · Accepted solution]

@JDBOS , I think I have given you the measure you can use. Just make sure table name is correct for clientType

sumx( values('Case Record [clientType]) ,

COALESCE(CALCULATE(

COUNTROWS('Case Record'),

CONTAINSSTRING('Case Record'[Related_Inquiry__r.Type_s_of_Victimization__c],"Domestic and/or family violence")

),0)* (FundingPercent[FundingPercent Value]/100))

[JDBOS]

@amitchandak this almost makes sense - although I've never used Sumx(values...

So , {sumx( values('Case Record [clientType]) ,} is added before the existing formula

COALESCE(CALCULATE(

COUNTROWS('Case Record'),

CONTAINSSTRING('Case Record'[Related_Inquiry__r.Type_s_of_Victimization__c],"Domestic and/or family violence")

),0)* (FundingPercent[FundingPercent Value]/100))

But for ('Case Record [clientType]) - does it matter what type of field I use for "clientType"?    In the existing formula, we're using a field from a related table...

I really appreciate your coaching on this!!