cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JDBOS
Helper III
Helper III

How to add a multiplication to an existing Calculate formula

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

JDBOS_0-1620390348376.png

using:  

JDBOS_1-1620390437352.png   a numeric calculated column

defined as:

JDBOS_2-1620390531671.png

 

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 😁

1 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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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?)

@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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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!! 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors