Using SUMX() to Count Values within Varying Periods (Quarters and Years)

[tachydidaxy]

I need to have a count of member accounts which have spent more than $90 in restaurant charges for a given quarter. 

I have a table called FBChecks which lists all the checks at our restaurant outlets and the memberid the check was charged to. So for instance filtering this table to look at one specific memberid (7872), for 2017-Q2 results in the following:

 

Ultimately, my desired visual for this report will look as follows:

However, right now in order for me to see every thing that's actually going on behind the scenes I've added two columns, one called "FandB Member Spending" that simply sums the total spent by the member in the current row context for the period in question, and one called "Number of Distinct FandB Members with Checks" which simply gives a distinct count of the member ids that have at least one check in the given period. This results in the visual shown below:

My issue is that even though the spending total per member is correct, as is the count of each member who had at least one F&B check in the given period, the last column which is supposed to count only the members whose total checks were more than $90 is not correct.

The last column "Who Has Summed Checks Total More than 90" is correct only when looking at the individual row context for each individual member. It correctly puts a 1 when the member for the current row has a total of greater than $90 and leaves it blank if the total is less than $90.

The problem is that at the Q2 and 2017 row context it is not counting only the members whose total check amounts were greater than $90 and is instead just counting all the members who had at least one check. Therefore, right now at the Q2 and 2017 levels, the totals are always the same for "Number of Distinct Members with FandB Checks" and the "Who Has Summed Checks Total More than 90" columns. 

For instance in the screen grab below, even though the highlighted cells in the "Who Has Summed Checks Total More than 90" column are blank (no values), they are getting summed into the total for the Q2 row.

The actual total for Q2 for the "Who Has Summed Checks Total More than 90" should be 955. As you can see from the screen grab below - when I select the entire column and look at the autosum and autocount values in Excel's taskbar it shows the correct amount. 

I'm not sure what I may be doing wrong. Here are the formulas for my measures.

FandB Member Spending :=
CALCULATE (
    SUM ( 'FBChecks'[amount] ),
    FILTER (
        FBChecks,
        FBChecks[check_member_type] = "Social"
            || FBChecks[check_member_type] = "Comprehensive"
    )
)

Number of Distinct FandB Members with Checks :=
CALCULATE (
    DISTINCTCOUNT ( FBChecks[member_id] ),
    FILTER (
        FBChecks,
        FBChecks[check_member_type] = "Comprehensive"
            || FBChecks[check_member_type] = "Social"
    )
)

Who Has Summed Checks Total More than 90 :=
CALCULATE (
    DISTINCTCOUNT ( FBChecks[member_id] ),
    FILTER (
        FBChecks,
        FBChecks[check_member_type] = "Comprehensive"
            || FBChecks[check_member_type] = "Social"
    ),
    FILTER ( FBChecks, SUMX ( FBChecks, FBChecks[amount] ) >= 90 )
)

I'm sure I'm missing something that's probably easy to spot. Any advice would be greatly appreciated.

I've tried doing the following, but I get an error every time I try and add it to my pivot table stating that the "formula is invalid":

Corrected Count:=SUMX( DISTINCT( Calendar[dates] ), [Who Has Summed Checks Total More than 90] ) 

[Ashish_Mathur]

Hi,

It is difficult to understand your question without seeing your Excel file.  Share the download link and also show your expected result there.

[tachydidaxy]

Thanks Ashish - please find workbook attached here.

On Sheet 1, the total for 2017 - Q2 - "Who Has Summed Checks Total More than 90" column should be 956 which you can verify by selecting the entire column and validating with the autosum/autocount totals in Excel's status bar.

---

@Ashish_Mathur wrote:

Hi,

It is difficult to understand your question without seeing your Excel file.  Share the download link and also show your expected result there.

---

[Ashish_Mathur · Accepted solution]

Hi @tachydidaxy,

Try this formula

=COUNTROWS(FILTER(SUMMARIZE(FILTER(FBChecks,FBChecks[check_member_type]="Comprehensive"||FBChecks[check_member_type]="Social"),FBChecks[member_id],"ABCD",SUM(FBChecks[amount])),[ABCD]>90))

Hope this helps.

[tachydidaxy]

Thanks so much @Ashish_Mathur – this got the exact results I needed.

I did stumble across the SUMMARIZE() function in my google-fu, but was struggling with how to reference a specific column from within the summarized table.

I see that you simply used a name that was not used in any of the original tables (e.g. "ABCD") and then were able to simply reference it using [ABCD] in the filter function (e.g. [ABCD] > 90).

I know ideally the suggestions I've read are to always precede a column name with its table name (e.g. Table[Column]) and to not precede measure names  (e.g. [Measure]); that way one can immediately tell what's being referenced when reviewing DAX code.

Is there a way to continue to use those best practice suggestions while referencing columns from within a summarized table? Can you give a summarized table a name? Or is the summarized table only really used to pass to other functions that accept tables? Curious as to your insight here.

Thanks again.

---

@Ashish_Mathur wrote:

Hi @tachydidaxy,

Try this formula

=COUNTROWS(FILTER(SUMMARIZE(FILTER(FBChecks,FBChecks[check_member_type]="Comprehensive"||FBChecks[check_member_type]="Social"),FBChecks[member_id],"ABCD",SUM(FBChecks[amount])),[ABCD]>90))

Hope this helps.

---

[Ashish_Mathur]

Hi,

You are welcome.  I do not know whether a summarised table can be given a name or not.  May be someone else can help here.