Count Occurrence in a Column- must be dynamic

[aflintdepm]

I have a column of data with recurring entries.  There are about 10 unique choices, but these can occur an infinite of times.  

I would like to count how many times each reason appears, but have the calculation add Reason 4 or 5 whenever they are added in the future.

Ideally, I would want something like this (at a minimum) in a matrix

I would also like to be able to calculate percent of total to visualize in a pie.

I feel like, once I can work out how to get the count, I can expand to other presentations.

I tried this formula, but it is giving me a blank matrix when I add it as a value.

Count of Reason = CALCULATE( COUNTROWS('ExampleTable'),FILTER('ExampleTable','ExampleTable'[Reason] = SELECTEDVALUE('ExampleTable'[Reason])))

This seems like it should be easier...

Thanks

[aflintdepm]

Thank you @Padycosmos , @Ashish_Mathur , and @bharath_v 

Apparently, Power BI decided to throw me a real curve ball.  The tab I was working in appears to have been corrupted.  I started testing with other visuals that I knew worked in other tabs and those returned "blank" as well.  I finally just deleted the whole tab and made a new one and now everything works as expected.  Spent 3 days trying to figure this out....🤕

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and SIngle) from the Date column of the Data Table to the Date column of the Calendar Table.  To the matrix visual, drag Date from the Calendar Table to row labels and Reason to Column labels.  Write this measure and drag it to the visual

Count = countrows(Data)

Count (%) by date = calculate([Count],all(calendar))

Count (%) by Reason = calculate([Count],all(Data[Reason]))

Format the last 2 measures as %.

Hope this helps.

[aflintdepm]

@Ashish_Mathur 

This got me closer, but is not working properly

1. I had a calendar table identified already, so that part worked.  However, when I add it to the matrix, it retruns dates that are not in the fact table.  My data is only from 12/29/22, but the matrix is returning values from 6/1/20
2. Only the Count % by Date is returning results.  The % Count by Reason is blank in all columns
3. The Count % by Date is returning multiple results >100%, so something is wrong there

I know it is difficult to assist without sample data to manipulate, so I truly appreciate your help.

[Ashish_Mathur]

I will need to see your file.

[bharath_v · Accepted solution]

Hi @aflintdepm 

I was able to simulate the output without using any DAX.

In matrix visual, do these steps:

Rows -> Date

Columns -> Reason

Values -> Count of Reason (not distinct)

           -> Count of Reason (Show value as percentage of total)

Rename the value headers accordingly.

Output:

Let me know if I am missing something. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aflintdepm]

I'm not sure you're missing anything, but I definitely am

Not sure what I'm doing wrong

[bharath_v]

Can you check on the filters section to see if you are filtering data?

[aflintdepm]

No slicers on the page, no filters on the visual, as far as I can tell

Does it have anything to do with the fact that the Reason column is text?

[Padycosmos]

Hope this helps:

[aflintdepm]

Thank you for the example- unfortunately, I'm getting a blank matrix.  Can you please describe which value you put in Row/Column/Value?  Thank you

[Padycosmos]

Dates in the Row, and Category in Column and Measure in values