Power BI Table Group Total

[surfingjoe]

Power BIExcelThe Power BI photo is simply a count patients per day and created a date grouping by month.  Each Month the count is correct but the total is not a grand total (possibly the distinct count for all the months of the measured dates).  The "Excel" formula its showing the "Grand Total" or another words the sum of all the counts in the above rows.  Is there a way to force the total for a measure to become a "Grand Total" instead of what ever method the visual comes up with for that Visual????

[Anonymous · Accepted solution]

The v-shex-msft idea is good one, but you have to update ISFILTERED with ISCROSSFILTERED.

Supposing [Distinct Patients] is your original measure, you can create another measure

 Alternate Patients =
IF (
    ISCROSSFILTERED ( Table[Month] ),
    [Distinct Patients],
    SUMX (
        SUMMARIZE ( ALLSELECTED ( Table ), Table[Month], "DP", [Distinct Patients] ),
        [DP]
    )
)

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

[surfingjoe · Accepted solution]

In case anyone reads about the issue of using distinct count in Power BI Visuals and discovering the totals to not be a sum of your counts and instead of a SUM Total of the counts, Power BI provides the grand total as a distinct count of the entire range of dates, then I'm summarizing the solution below.

In this case, I wanted a distinct count of patients per month and my original formula when filtered in a visual per month the data per month was corrrect.  The original formula being 

patient count = DISTINCTCOUNT(Source[Patient Full Name])

Problem with the above is with a visual's grand total.  The visual's grand total will be a distinct count of patients in the entire year, not a sum of the counts per month.

The answer is extremely simple, but eluded me because I've not used the dax function "value" previously.  The replies in this forum and another forum led me to the solution.  Thank everyone for the great inputs!!

The solution is as follows:

Total Patient Count = SUMX(VALUES(Source[Date]),[patient count])

Basically use the value of the original patient count formula and use sumx that creates a virtual table of the Values, which I can then put into any visual which then uses the summary of the VALUE for corresponding filter (in this case per month).  Because it is a value, the visual will summarize the values as a sum total as the visual's grand total. 

[surfingjoe · Accepted solution]

In case anyone reads about the issue of using distinct count in Power BI Visuals and discovering the totals to not be a sum of your counts and instead of a SUM Total of the counts, Power BI provides the grand total as a distinct count of the entire range of dates, then I'm summarizing the solution below.

In this case, I wanted a distinct count of patients per month and my original formula when filtered in a visual per month the data per month was corrrect.  The original formula being 

patient count = DISTINCTCOUNT(Source[Patient Full Name])

Problem with the above is with a visual's grand total.  The visual's grand total will be a distinct count of patients in the entire year, not a sum of the counts per month.

The answer is extremely simple, but eluded me because I've not used the dax function "value" previously.  The replies in this forum and another forum led me to the solution.  Thank everyone for the great inputs!!

The solution is as follows:

Total Patient Count = SUMX(VALUES(Source[Date]),[patient count])

Basically use the value of the original patient count formula and use sumx that creates a virtual table of the Values, which I can then put into any visual which then uses the summary of the VALUE for corresponding filter (in this case per month).  Because it is a value, the visual will summarize the values as a sum total as the visual's grand total. 

[surfingjoe · Accepted solution]

In case anyone reads about the issue of using distinct count in Power BI Visuals and discovering the totals to not be a sum of your counts and instead of a SUM Total of the counts, Power BI provides the grand total as a distinct count of the entire range of dates, then I'm summarizing the solution below.

In this case, I wanted a distinct count of patients per month and my original formula when filtered in a visual per month the data per month was corrrect.  The original formula being 

patient count = DISTINCTCOUNT(Source[Patient Full Name])

Problem with the above is with a visual's grand total.  The visual's grand total will be a distinct count of patients in the entire year, not a sum of the counts per month.

The answer is extremely simple, but eluded me because I've not used the dax function "value" previously.  The replies in this forum and another forum led me to the solution.  Thank everyone for the great inputs!!

The solution is as follows:

Total Patient Count = SUMX(VALUES(Source[Date]),[patient count])

Basically use the value of the original patient count formula and use sumx that creates a virtual table of the Values, which I can then put into any visual which then uses the summary of the VALUE for corresponding filter (in this case per month).  Because it is a value, the visual will summarize the values as a sum total as the visual's grand total. 

[v-shex-msft]

HI @surfingjoe ,

You can try to use following measure if it works. (I replace normal total level calculation with a specific formula )

Measure =
IF (
    ISFILTERED ( Table[Date] ),
    COUNTROWS ( VALUES ( Table[Patient] ) ),
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( Tabel ),
            [Date],
            "DC", COUNTROWS ( VALUES ( Table[Patient] ) )
        ),
        [DC]
    )
)

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

The v-shex-msft idea is good one, but you have to update ISFILTERED with ISCROSSFILTERED.

Supposing [Distinct Patients] is your original measure, you can create another measure

 Alternate Patients =
IF (
    ISCROSSFILTERED ( Table[Month] ),
    [Distinct Patients],
    SUMX (
        SUMMARIZE ( ALLSELECTED ( Table ), Table[Month], "DP", [Distinct Patients] ),
        [DP]
    )
)

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

[surfingjoe]

Just so you know, your solution works except the "allselected" portion of the formula.  I did the edit as follows:

Patient count 2 =

if (

ISCROSSFILTERED(Source[Month & Year]),

[patient count],

sumx(

SUMMARIZE(Source,Source[Month & Year],"DP",[patient count]),

[DP]

)

)

and it worked.  However, now I've got to figure out the distribution of patient count per location, per drug etc and the above formula isn't the final solution to simply determine patient count per day and use that measure filtered per location or per drug.  Ughhhhh