Unable to Get Sum Total in a Matrix Visual

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

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

16 Replies

Anonymous
Not applicable
6 years ago
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
  Community Support
  6 years ago
  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
tex628
Community Champion
6 years ago
Whats the dax of your [returning_views_format]?
- Anonymous
  Not applicable
  6 years ago
  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
    Community Champion
    6 years ago
    MFelix's solutiosn should work like a charm!
MFelix
Super User
6 years ago
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
  Not applicable
  6 years ago
  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?
  - tex628
    Community Champion
    6 years ago
    Anonymousin your table 'ExecutionLog3' are there more than one row for each user?
v-lionel-msft
Community Support
6 years ago
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.