Unable to Get Sum Total in a Matrix Visual

[Anonymous]

Hi All,

I have a matrix visual with several measures added to it as shown below.

My issue is with returning_viewers. It doesn't show the sum like the others. What can I do?

returning_viewers = 
var return_viewers_exist = 1
return 
IF([returning_views_format] > 0, return_viewers_exist, 0)

[Anonymous · Accepted solution]

Hi All,

Thanks for all your support here. I was able to finally solve the issue. I did the following:

viewers_count = IF([returning_views_format] > 0, 1, 0)

returning_viewers = 
var return_viewers_exist = 1
var Viewers_count = IF([returning_views_format] > 0, return_viewers_exist, 0)
return 
If (HASONEFILTER('ExecutionLog3'[UserName]),  Viewers_count, SUMX(DISTINCT('ExecutionLog3'[UserName]), [viewers_count]))

Best Regards,

Divo

[v-lionel-msft]

Hi @Anonymous ,

If your problem was solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,
Lionel Chen

[v-lionel-msft]

Hi @Anonymous ,

In fact, this problem is very common.

There are many problems about this on the forum. You can search for the keyword "total wrong/ table total is wrong/matrix total is wrong".

To solve this problem, we may need to know your data model and all the related DAX formula you used in [returning_viewers] measure .

In addition to the solutions @MFelix  provides, you can also refer to these two articles:

Subtotals and Grand Totals That Add Up “Correctly” 

Power BI: Totals Incorrect 

Best regards,
Lionel Chen

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

[MFelix]

Hi @Anonymous ,

Measure are based on context so when you add a measure to a table the total calculation is based on the measure you are calculating and not a sum or a average. You need to add a aggregator function to your measure in order to return the sum of total value.

returning_viewers = 
var return_viewers_exist = 1
var Viewers_count = IF([returning_views_format] > 0, return_viewers_exist, 0)
return 
If (HASONEFILTER(Table[Column]; Viewers_count; SUMX(Table; Viewers_count))

This should give expected result, just change the Table[Column] part by a column that you use to make the line values on the table.

[Anonymous]

Hi @MFelix,

Thanks for your solution. It is looking promising. Below is what I get after trying it out.

returning_viewers = 
var return_viewers_exist = 1
var Viewers_count = IF([returning_views_format] > 0, return_viewers_exist, 0)
return 
If (HASONEFILTER('ExecutionLog3'[UserName]), Viewers_count, SUMX('ExecutionLog3', Viewers_count))

It is displaying 114 instead of 11. When you count all the count all the "1's" in returning_viewers column, I was expecting to see 11 as the total. Please what am I missing?

[MFelix]

Hi @Anonymous ,

Try the following changes to your measure:

returning_viewers = 
var return_viewers_exist = 1
var Viewers_count = IF([returning_views_format] > 0, return_viewers_exist, 0)
return 
If (HASONEFILTER('ExecutionLog3'[UserName]), Viewers_count, SUMX(ALLSELECTED('ExecutionLog3'[UserName]), Viewers_count))

[Anonymous]

Hi @MFelix ,

Thanks for your reply. With this change I get 628 isntead of 11. What are we missing?

[MFelix]

Hi @Anonymous ,

This as to do with the setup of the data (having more than one line per name) and the way the measures interact. 

Based on the formula you have does your row will always return 1 on line level? 

Can you share a sample file?

[Anonymous]

Hi @MFelix ,

I am wondering if the issue is arising from the calculation of returning_views_format. The measure for this is like below:

returning_views_format = COUNT(ExecutionLog3[UserName]) - DISTINCTCOUNT(ExecutionLog3[UserName])

so

Viewers_count can also be written as:

var Viewers_count = IF ( COUNT(ExecutionLog3[UserName])-DISTINCTCOUNT(ExecutionLog3[UserName]) > 0, 1, 0)

Can the issue be as a result of the above?

[tex628]

Try this:

returning_viewers = 
var return_viewers_exist = 1
var Viewers_count = IF([returning_views_format] > 0, return_viewers_exist, 0)
return 
If(
HASONEFILTER('ExecutionLog3'[UserName]),
Viewers_count, 
SUMX(
SUMMARIZE('ExecutionLog3',
'ExecutionLog3'[UserName],
"Value",
Viewers_count)),
[Value]
))

[MFelix]

Hi @Anonymous ,

Yes it can as I refered measure are based on context so the change of the measure changes the result.

[tex628]

@Anonymousin your table 'ExecutionLog3' are there more than one row for each user? 

[Anonymous]

Hi @tex628 ,

Yes there are more than 1 row for each user.

[tex628]

Whats the dax of your [returning_views_format]?

[Anonymous]

Hi @tex628 ,

Below is the dax:

returning_views_format = CALCULATE(IF (
    [returning_views] = BLANK (),
    0,
    [returning_views]
))

returning_views = 'Measure_Usage_Metrics'[count_total_users] - [count_unique_users]

[tex628]

@MFelix's solutiosn should work like a charm!