Forum Discussion

harirao's avatar
harirao
Post Prodigy
6 years ago
Solved

Total not Matching

Hi All,
After including DISTINCTCOUNT & Filter in measure, Total is not coming correctly.

Correct total is 209, but getting 206

61
31
27
25
17
16
11
7
7
7
Total209

 

Please help me to get correct answer.

Thank you.

Regards,
Hari

Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi harirao ,

     

    It's a subtotal issue,pls add a measure as below:

     

    _%6week_1&2 = SUMX(SUMMARIZE('Query1','Query1'[subregion]),'Query1'[%6week_1&2])

     

    And you will see:

    For the related .pbix file,pls click here.

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

6 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    It is nearly impossible for me to assist when 50% of your measure is blanked out.

     

    What I suspect is happening though is your total, which is ignoring the subregion, has some duplicate data without the subregion in the filter context, so DISTINCTCOUNT is getting rid of those 3 records.

     

    Totals in a matrix or table are not totaling the data above, but redoing the measure over the entire table.

     

    If you need further help, please post some data and your actual measure.

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

    • harirao's avatar
      harirao
      Post Prodigy
      6 years ago

      Hi edhans,

      Please find the sample data as well as actul measure for your reference.

      reportdateactual_visibility_Weeks0 visibility exclusions1MUSD FlagPipelineExclusionIndicatorsubregionOpportunityId
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20200Yes0NNorth West EuropeOPE-0006287232
      5/25/20202Yes0NNorth West EuropeOPE-0006287232
      5/25/20203Yes0NWWOPE-0006287232
      5/25/20204Yes0NWWOPE-0006287232
      5/25/20205Yes0NWWOPE-0006287232
      5/25/20206Yes0NWWOPE-0006287232
      5/25/20207Yes0NWWOPE-0006287232
      5/25/20208Yes0NWWOPE-0006287232
      5/25/20209Yes0NWWOPE-0006287232
      5/25/202010Yes0NWWOPE-0006287232
      5/25/202011Yes0NWWOPE-0006287232
      5/25/202012Yes0NWWOPE-0006287232
      5/25/202013Yes0NWWOPE-0007366622
      5/25/202014Yes0NNorth AmericaOPE-0007366622
      5/25/20200Yes0NEurope SouthOPE-0007366622
      5/25/202025Yes0NNorth AmericaOPE-0007366622
      5/25/202025Yes0NEurope SouthOPE-0007366622
      5/25/202025Yes0NEurope SouthOPE-0007366622
      5/25/202025Yes0NNorth AmericaOPE-0007366622
      5/25/202025Yes0NEurope SouthOPE-0007366622
      5/25/202025Yes0NCERTAOPE-0007366622
      5/25/202025Yes0NEurope SouthOPE-0007366622
      5/25/20200No1NAPACOPE-0011203108
      5/25/20200No1NAPACOPE-0011203108
      5/25/20201No1NCERTAOPE-0010571141
      5/25/20201No1NCERTAOPE-0010571141
      5/25/20205No1NIndiaOPE-0005645198
      5/25/20207No1NIndiaOPE-0005645198
      5/25/20202Yes1NLatin AmericaOPE-0010964870
      5/25/20201Yes1NLatin AmericaOPE-0011184958
      5/25/202060Yes1NLatin AmericaOPE-0010542404
      5/25/202060Yes1NLatin AmericaOPE-0010542412
      5/25/2020116Yes1NNorth AmericaOPE-0008743286
      5/25/202059Yes1NNorth AmericaOPE-0010959914
      5/25/202057Yes1NNorth AmericaOPE-0008743288
      5/25/202059Yes1NNorth AmericaOPE-0008743296
      5/25/202059Yes1NNorth AmericaOPE-0008743270
      5/25/202081Yes1NNorth West EuropeOPP-0003975830
      5/25/20208Yes1NNorth West EuropeOPE-0011102824
      5/25/202011Yes1NNorth West EuropeOPE-0011100421

       

       

       



      Thank you

      Regards,
      Hari 

       

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi  harirao ,

     

    Try to add a measure as below:

     

    measure=sumx(summarize('table',[subregion],"%6",[%6week_1&2],[%6])

     

     

    Check whether it works.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
      • harirao's avatar
        harirao
        Post Prodigy
        6 years ago

        Hi v-kelly-msft 

        I tried with below measure, was able to fine that three subregion having same ID which is creating issue for WW
        Correct Answer is 89(59+9+21)


        Other Subregions are getting correct answer (15+19+5+27+6+28+7=107)



        Measure of %6week_1&2

        %6week_1&2 = CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]), FILTER(Query1, Query1[actual_visibility_weeks]> 0))
        +
        CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]), FILTER(Query1, Query1[0 visibility exclusions]= "No"),
        FILTER(Query1, Query1[actual_visibility_weeks]= 0),
        FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N"))

         

        Can you please help me to get correct result for WW.

        Thank you

        Regards,
        Hari