Forum Discussion

Vizbaby's avatar
Vizbaby
Icon for Helper I rankHelper I
1 year ago
Solved

Percentage Total is not accurate

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])))
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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,

10 Replies

  • SundarRaj's avatar
    SundarRaj
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Vizbaby
      Icon for Helper I rankHelper I
      1 year ago

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

      • SundarRaj's avatar
        SundarRaj
        Icon for Super User rankSuper User
        1 year ago

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

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Vizbaby
      Icon for Helper I rankHelper I
      1 year ago

      one of my formula already has SUMX and it didnt work

      • audreygerred's avatar
        audreygerred
        Icon for Super User rankSuper User
        1 year ago

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