Forum Discussion

Roym's avatar
Roym
Icon for Helper IV rankHelper IV
4 years ago
Solved

Unique and not unique count

I have a table in my dataset with Issues and Findings per Reporting Entity. The count of those are added in a table (see screenshot below. In this case two issues are linked to both reporting entity Europe and USA. What my PowerBI report table does is show a '1' in both the Europe and USA row (that is what I want), and for the Total row it shows the total 'Unique' issues. I also want that, but next to that I need a 'not unique total'. So in thise case I would like to see:

 

Reporting EntityIssuesFindingsTotal
Europe314
USA141933
Africa202
Total192039
Total Unique182038

 

 

I'm not sure how I can add that extra row of information. Anyone an idea for this? Thanks!!

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Roym 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.

    • Roym's avatar
      Roym
      Icon for Helper IV rankHelper IV
      4 years ago

      Greg_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:

       

       IssuesFindings
      Total32
      Total unique22

       

      Hopefully this explains my question a bit better. 

      Test.pbix 

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        4 years ago

        Hi Roym 
        It seems you have a Total issue, so please see this post from Greg_Deckler :

        Dealing with Measure Totals

         

        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!!