Forum Discussion

andicornejo12's avatar
andicornejo12
Helper II
2 years ago
Solved

Row Count Sum

Hello Community,

 

I am stuck here. The request is to have a 1 in every row of a table built in the report, but the sum gives the total of those rows.

For showing the "1" everywhere, I added a custom column equals to 1 to the fact table, I then summarized the column by distinctcount but my result for the total is 1 also (I would need here the total row count like in column 3). The third column is a measure 

Row Count = SUM('_CiQ Milestone'[RowCount]) that gives me the right total but is not showing me a 1 in each row. Can someone help me with this request?
 
 
Thank you!

 

 

@Dax help
  • talespin's avatar
    talespin
    2 years ago

    hi andicornejo12 ,

     

    Please use this measure, replace it with your column. If there are other columns in your visual, might need to modify measure. Ignore the custom column in second screenshot(not required).

     

    MCount =
    IF(HASONEFILTER( TestTbl4[AWBNo] ), 1, COUNT( TestTbl4[AWBNo] ) )
     
     

     

     

14 Replies

    • andicornejo12's avatar
      andicornejo12
      Helper II
      2 years ago

      Hi talespin 

       

      Thank you so much for your answer. 

      Yes, what you are showing in the picture is what I need to show.

      However, the problem is that AWB number can be repeated many times in the fact table, for example the AWB number 14 in your example table can show up twice in the table depending on the dimension you want to break it into. Therefore your solution is giving me the same result as the third column in my example 😞

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi andicornejo12 ,

     

    Please use this measure, replace it with your column. If there are other columns in your visual, might need to modify measure. Ignore the custom column in second screenshot(not required).

     

    MCount =
    IF(HASONEFILTER( TestTbl4[AWBNo] ), 1, COUNT( TestTbl4[AWBNo] ) )
     
     

     

     

    • andicornejo12's avatar
      andicornejo12
      Helper II
      2 years ago

      hi talespin 

       

      Thanks again for your help.

      Your formula almost works is showing a one and the total.

      However, the request is a bit more complicated, in my example I used AWB Number but the request is that row count should work with any column in the table. See with AWB version below, is not working anymore (your solution is RowCount1 in red)

       

       

      • talespin's avatar
        talespin
        Solution Sage
        2 years ago

        hi andicornejo12 ,

         

        "However, the request is a bit more complicated, in my example I used AWB Number but the request is that row count should work with any column in the table."

         

        Are the columns from same table or can be from different table?

         

        Try this if columns in visual are from same table. Test the total count.

        IF( ISFILTERED(TestTbl4), 1, COUNT( TestTbl4[AWBNo] ) )
    • talespin's avatar
      talespin
      Solution Sage
      2 years ago

      I don't think this is the solution. Requester wanted this to work no matter what column is in visual(same table to another table). 

      • andicornejo12's avatar
        andicornejo12
        Helper II
        2 years ago

        talespin no is not the full solution as you said, the request is that it works with any column but I dont think there is other way :S

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, andicornejo12 

     

    i think Distinctcount() function work as you want
    make a measure

    measure =
distinctcount(tablename[awb number])
    • andicornejo12's avatar
      andicornejo12
      Helper II
      2 years ago

      hi Dangar332 

       

      Thanks for your reply. I already tried distinctcount. The problem with this, is the second column of my table above. It doesnt give me the total, the total is shown as 1 as well 😞