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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors