Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper III
1 year ago
Solved

YTD DistinctCount not totaling correctly

I have this table attached to a slicer and everything after January for the YTD# column(distinctcount member number) seems to subtract 1 from the overall total row.  Please advise.  I have tried everything I can think of.  The YTD# total count should be 1,817 if you manually add the 2 rows above.  Currently it shows 1,816.  All other columns look correct except this one.

Edit: I just noticed the February monthly # column is doing the same thing....

 

 

 

 

Below are my current measures/calculated columns:

 

Existing/New = IF(Heloc3MonthAdvances[OpenDateMonth] < Heloc3MonthAdvances[TransactionDateMonth],"Existing HELOC","New HELOC")
 
MemberNumberYTD DISTINCTCOUNT = SUMX (
    VALUES ( Heloc3MonthAdvances[TransactionDate].[Month]),TOTALYTD(
        [Consider HELOC Advances Count Sub] + 0,
    DateTable[Date]))
 
Consider HELOC Advances Count Sub = CALCULATE(DISTINCTCOUNT(Heloc3MonthAdvances[MemberNumber]),Heloc3MonthAdvances[Consider HELOC Advances Sub] =1)
 
Consider HELOC Advances Sub = if(CALCULATE(COUNTROWS(Heloc3MonthAdvances),FILTER(Heloc3MonthAdvances,Heloc3MonthAdvances[OpenDateMonth]=EARLIER(Heloc3MonthAdvances[TransactionDateMonth])),FILTER(Heloc3MonthAdvances,Heloc3MonthAdvances[MemberNumber]=EARLIER(Heloc3MonthAdvances[MemberNumber])))>1,if(Heloc3MonthAdvances[Existing/New] = "New HELOC",1,0),1)
 

Advances Table pbix 

 

Here's a stripped down version with the data randomized for testing - thank you

 

The YTD # column is not totalling correctly across all months YTD

 
Thank you for your help!!! This forum has always been a great help šŸ™‚
 
 
 
 
 
 
 

 

 

Need Help
  • Ashish_Excel's avatar
    Ashish_Excel
    1 year ago

    Hi,

    The problem is in Feb.  The # adds upto 426 though it should add up to 427.  It means that there is a member being considered both in new and old in that month itself.  Check your data.  Make the corrections mentioned in my previous post.

     

11 Replies

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi,

    Not sure of what you are trying to do.  Share the download link of the PBI file.  Show the problem and the expected result.

    • mclawler's avatar
      mclawler
      Icon for Helper III rankHelper III
      1 year ago

      Advances Table pbix 

       

      Here's a stripped down version with the data randomized for testing - thank you

       

      The YTD # column is not totalling correctly across all months YTD

      • Ashish_Excel's avatar
        Ashish_Excel
        Icon for Solution Supplier rankSolution Supplier
        1 year ago

        Hi,

        Change the relationship to Many to One (from Many to Many).  Simplify your measure to

        MemberNumberYTD DISTINCTCOUNT = CALCULATE([Consider HELOC Advances Count Sub],DATESYTD(DateTable[Date]))

        The answer will still be 926 and 138 and the total is 1002.  Since it is a distinctcount, the answer cannot be additive.  If you really want to add the 2 distinctcount values, then write this measure

        Measure = SUMX(VALUES(Heloc3MonthAdvances[Existing/New]),[MemberNumberYTD DISTINCTCOUNT])

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello mclawler ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    Ashish_Excel Thank you for your quick response.

     

    mclawler, as Ashish_Excel mentioned,to better understand your scenario and provide more accurate suggestions, could you please share a small sample of your dataset along with the expected output? Please make sure to remove any sensitive or confidential information.

     

    This will help the community replicate your issue or use case more effectively and offer specific guidance.

     

    Regards,

    B Manikanteswara Reddy

    • mclawler's avatar
      mclawler
      Icon for Helper III rankHelper III
      1 year ago

      Advances Table pbix 

       

      Here's a stripped down version with the data randomized for testing - thank you

       

      The YTD # column is not totalling correctly across all months YTD

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hello mclawler ,

         

        Thank you for sharing the details! Just to confirm ,  the super user Ashish_Excel  answer is absolutely correct. The key issue lies in the relationship type between your tables.

        Please change the relationship from many-to-many to many-to-one between Heloc3MonthAdvances and DateTable. Once that's corrected, the measures suggested by the super user will work as intended and will give you the correct YTD total.

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" ā€“ Iā€™d truly appreciate it!

        Regards,

        B Manikanteswara Reddy

         

         

        Regards,

        B Manikanteswara Reddy