Percentage Total is not accurate

[Vizbaby]

The percentage column for my viz is not adding up correctly. 

I have used the formulas below but i keep getting same result. The first two columns are the likely correct result but total is  not correct. please help.

FYI -Both tables have relationship and EVEN when response rate is calculated using one table, still encountering same problem.

2NewResp_Rate = SUMX(SUMMARIZE(Campaign,Campaign[numberofcontacts]),CALCULATE(DISTINCTCOUNT(New_MasterQuery[hc_sc_id])))

Response_rate = VAR TotalResponses=countrows(New_MasterQuery)

VAR TotalContacts=sum(Campaign[numberofcontacts])

RETURN DIVIDE(TotalResponses,TotalContacts,BLANK())

NewResp Rate = CALCULATE(DIVIDE(COUNT('New_MasterQuery'[hc_sc_id]),sum(Campaign[numberofcontacts])))

[Vizbaby]

Thanks so much, it worked

[audreygerred]

Are you wanting to have the percentage summed for the total in your percentages? If so, you will need to incorporate sumx with that so that it calculated each percentage at the row, then sums them up for the total.

[Vizbaby]

one of my formula already has SUMX and it didnt work

[audreygerred]

I mean you will have to take you response rate or new response rate and do a SUMX in addition to the formula you have - that way it will do the division at the row level, then sum it for the total. The one where you have the sumx is just a distinct count - it's not doing any division to do a percentage.

Something like this (without having the sample data I can't confirm it, but something this would figure out the percent for each row then sum them)

Response_rate =

VAR TotalResponses = COUNTROWS(New_MasterQuery)

VAR TotalContacts = SUM(Campaign[numberofcontacts])

VAR RowPercentage = DIVIDE(TotalResponses, TotalContacts, BLANK())

RETURN

    IF(

        ISINSCOPE(Campaign[YourGroupingColumn]),

        RowPercentage,

        SUMX(

            VALUES(Campaign[YourGroupingColumn]),

            VAR Responses = CALCULATE(COUNTROWS(New_MasterQuery))

            VAR Contacts = CALCULATE(SUM(Campaign[numberofcontacts]))

            RETURN DIVIDE(Responses, Contacts, BLANK())

        )

    )

[Vizbaby]

Thank u, i just tried your formula and the answer is same as the last column i have on the snapshot i shared, 102. its not giving the right result either

[SundarRaj]

Hi @Vizbaby,

To get the Percentages in the measure as the final total:

What you could do over here is create a Variable of a Summarised Table with the Percentage Column as against the Vendor Job.

Use IF and HasOneFilter function to replace the total value with Sum of the Percentage Column in the summarized table. This is a very high level picture of it. Let me know if you need a granular view. Thanks

Regards,

[Vizbaby]

Please can u give me granular level? i have the formulas i tried on my post. 

[SundarRaj]

Is it possible for you to share the data that you're working with or any dummy data in the same format?

[Vizbaby]

The columns in yellow is what im using to calc the rate 

[SundarRaj · Accepted solution]

Hey @Vizbaby ,
I'll attach a file link below. Please have a look at the DAX Code and Data Model to see the fundamental of getting the totals. Let me know in case of any queries. Thanks

Regards,