Forum Discussion

texasbiuser's avatar
texasbiuser
Frequent Visitor
6 years ago

GroupBy and SUM

I am trying to group data and then summaraize said date and am having issues with the measures. An example of what I have:   Date: the transaction date Check #: uniquie ID, but note that check num...
Need Help
Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

texasbiuser ,

 

Here is my file  PBIX.

 

My table is abbreviated from your sample.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel 

texasbiuser's avatar
texasbiuser
Frequent Visitor
6 years ago

Thanks, so this get's me the number of checks per day. Although, I did change the formula up to DISTINCTCOUNT, vs COUNT:

 

NumberofChecks = DISTINCTCOUNT(Sales[Check_Number])

The problem is the grand total. See below. This gives me the daily count for each day, but because check numbers are reused daily it does not properly count for the grand total of a month.

 

 

 

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    texasbiuser ,

    Not sure how the tables are set up, but see below or you may have to use FILTER.

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    NumberofChecks = CALCULATE(DISTINCTCOUNT(Sales[Check_Number], [DATE])
  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi texasbiuser ,

    This is a question about measure total, you can try the following measure:

    NumberofChecks =
SUMX (
    VALUES ( Sales[Date] ),
    CALCULATE ( DISTINCTCOUNT ( Sales[Check_Number] ) )
)

    For more details, the following posts may be helpful:

    Dealing with Measure Totals

    Measure Totals, The Final Word

     

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.