Solved: How to count non blank rows per column in a matrix... - Page 2

Anonymous · ‎09-13-2018

Hello all,

I have the following matrix and I would need to have a visualization that displays the count of non-blank Accounts for each month. What would be the best way to dsiaply this? and how could I calculate this?

MFelix · ‎09-17-2018

Hi @Anonymous,

You need to create a Dates table and relate that with your Visit table then create the following measures:

Average Visits = IF(AVERAGE(Visit[Velocity])= BLANK(); 0 ; AVERAGE(Visit[Velocity]))

Count Average = IF([Average Visits] = 0 ; 0; COUNT(Visit[Velocity]))

Then do two matrix visuals and add the month and the measures on your values.

If you don't want to show information futher than today then change your measures to:

Average Visits =
IF (
    MAX ( Dates[Date] ) >= TODAY ();
    BLANK ();
    IF ( AVERAGE ( Visit[Velocity] ) = BLANK (); 0; AVERAGE ( Visit[Velocity] ) )
)



Count Average =
IF (
    MAX ( Dates[Date] ) >= TODAY ();
    BLANK ();
    IF ( [Average Visits] = 0; 0; COUNT ( Visit[Velocity] ) )
)

See attach file with the second measures.

Hope this helps to get expected result.

Regards,

MFelix

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

View solution in original post