Forum Discussion
Helper IIICount Occurrence in a Column- must be dynamic
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.
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.
11 Replies
PadycosmosSolution Sage
Hope this helps:
aflintdepmThank 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
bharath_vResolver I
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?
- Ashish_Mathur
Super User
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
This got me closer, but is not working properly
- 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
- Only the Count % by Date is returning results. The % Count by Reason is blank in all columns
- 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.
- 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....🤕
