Forum Discussion
Helper IVUnique and not unique count
You can't add two "total" rows, but you can concatenate two result together like this. This will give the two results on all rows, but you can use IF(HASONEVALUE() or ISFILTERED() to show two results only in the "total".
Unique/Not Unique =
var u = <unique expression>
var n = <not unique expression>
return u & "/" & n
Pat
Community ChampionRoym Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Helper IVGreg_Deckler Sorry, you are right. I was a bit to quickly posting this. Please see attached an sample report thaat shows what I'm working with. As you can see in the report 'Issue1' is linked to both reporting entity Euope and USA. The table in my report therefore shows a '1' for Europe and a '1' for USA. That is exactly what I need. The total of the table shows the unique number of issues, that is also something that I want (in this case 2 issues and 2 findings). But as this doesn't add up table wise (1+1+1=2). I would like to have another row that shows the not unique count. So for my attached example:
| Issues | Findings | |
| Total | 3 | 2 |
| Total unique | 2 | 2 |
Hopefully this explains my question a bit better.
- VahidDM
Super User
Hi Roym
It seems you have a Total issue, so please see this post from Greg_Deckler :Fo your report, If you want to have a Not Unique Count, you can try something like this:
Total_Findings_DISTINCTCOUNT = IF ( HASONEVALUE ( Table1[Reporting Entity] ), CALCULATE ( DISTINCTCOUNT ( Table1[Issue name] ), FILTER ( Table1, Table1[Source] = "Finding" ) ), COUNTROWS ( FILTER ( Table1, Table1[Source] = "Finding" ) ) )Total_Issues_DISTINCTCOUNT = IF ( HASONEVALUE ( Table1[Reporting Entity] ), CALCULATE ( DISTINCTCOUNT ( Table1[Issue name] ), FILTER ( Table1, Table1[Source] = "Issue" ) ), COUNTROWS ( FILTER ( Table1, Table1[Source] = "Issue" ) ) )output for the not unique count:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
- Roym
VahidDM Thanks, this one works perfecly to calculate the non-unique totals! One last question, is it also possible to add an additional row that shows the unique numbers? So that you have two two rows for the totals?
- mahoneypat
Microsoft Employee
You can't add two "total" rows, but you can concatenate two result together like this. This will give the two results on all rows, but you can use IF(HASONEVALUE() or ISFILTERED() to show two results only in the "total".
Unique/Not Unique =
var u = <unique expression>
var n = <not unique expression>
return u & "/" & n
Pat
