How to count non blank rows per column in a matrix?

[Anonymous]

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?

[Jonnel]

Hi I want to ask how to get the total values inside my matrix table per Store Name?

Rows:       RIT_STORE_NAME

Columns: RIT_BRAND_NAME

Values:     Measure = IF(COUNT(Report_RD_RetailCallSheet_CSL_PNG_ASEAN_PH[Count])>0,1,0)

I want to know how many Brands does each Store Name has.

Thanks

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[MFelix]

Hi @Anonymous,

Without knowing your data is difficult to give you an answer, 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 (courtesy of @Greg_Deckler).

But looking at your needs you can make a measure like this:

Count nonblanks  = CALCULATE(COUNT(Table1[Account]);Table1[value] <> BLANK())

Then just add the month to the visual and this measure to values and should give what you want.

Regards,

MFelix

[Anonymous]

Hi @MFelix,

I definitively should've started with that!  can you tell it's my first time posting in a forum? haha so here is how the relationships looks like (below).

So, each Account has multiple store visits, and each store visit has a Velocity. There might be some months where the account has no velocity (hence the blanks and zeros in the data). The matrix that I posted has the average velocity per Account for each month. Now, I would need a way to display how many Accounts were visited each month; so I guess I need to count each distinct account that has a velocity for every month.

 Relationship between Account and Visits

[MFelix]

Did you tried the measure i suggested?

[Anonymous]

Hi @MFelix

It doesnt work. Here is an example of my data (the first two tables is my data and the third table is the desired outcome)

I need to have a table that displays the average velocity per account per month. While ignoring the blanks and zero value velocities. After i have that, i need to display (probably in a difeerent table) the number of accounts that were taken into consideration for the average calculation. In the example above, it'd be january = 3, February = 1, March = 0 and April = 1.

Now that i think about it, the original screenshot of the matrix i posted might be wrong as well...

I really appreciate the help! Hope this makes more sense.

[MFelix · Accepted solution]

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

[Ashish_Mathur]

Hi,

How do you arrive at 0.375 in cell J2?

[Anonymous]

Hi @Ashish_Mathur,

0.375 is the average Velocity of the account A

[Ashish_Mathur]

Hi,

Share the data tables here so that i can paste them in an Excel workbook.