Row Count Sum

[andicornejo12]

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!

[Dangar332]

hi, @andicornejo12 

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

measure =
distinctcount(tablename[awb number])

[andicornejo12]

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 😞

[Dangar332]

hi, @andicornejo12 

try below measure

countofid = IF(ISINSCOPE('Table'[id]),1,SUM('Table'[value]))

[andicornejo12]

hi @Dangar332 

This is almost perfect.!!! However only works with AWB Number if I substitute AWB number column with AWB version it doesnt work anymore :(.

Is there a way to use this formula to build a table from any column ?? see below first image is AWB number and is perfect, second image though is AWB version instead of AWB number and it doesnt work anymore. I need to have the AWB number in the table

[Dangar332]

hi, @andicornejo12 

not sure but just try 

nested if 

countofid = IF(
          ISINSCOPE('Table'[awb number]),
          1,
          IF(
            ISINSCOPE('Table'[awb vers no]),
            1,sum('Table'[awb vers no])
          )
          )

[talespin · Accepted solution]

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] ) )

[talespin]

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]

@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

[andicornejo12]

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]

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] ) )

[andicornejo12]

hi @talespin 

I appreciate so much the follow up. Thank you!!

However this formula is giving me teh same result as the previous one, a one in each row with a total but if I bring in AWB version instead of AWB number is not working anymore. I need to have AWB number in the table 😞

[talespin]

Hi @andicornejo12 

If possible, please share pbix file with all sensitive data removed.

[talespin]

hi @andicornejo12 ,

Is this the end result you are looking for?

[andicornejo12]

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 😞