Unique number column

[showard40k]

Unique number column

I have the following column of occurrences ranging from 1 to 5. What DAX do I need in my measure to capture the frequency of each number? I also want to report the total, and %.

My data sheet in PowerBi is called “Corrected”, can you please use this in your DAX response?

Occurrence
1
2
3
4
5
1
2
3
4
5

I want it to change to this:

Occurrence	Frequency	Percentage
1	2	20%
2	2	20%
3	2	20%
4	2	20%
5	2	20%
Total	10	100%

[samuelhoward]

Update: I figured out  I need to use the Matrix table, and NOT "table". When I put inthe three DAX measures you stated, my percentage is not working as they are all listed out as 100.00. Any thoughts? Can you check the code I am using, as I think the issue might be in there:

1. Frequency Measure: 

• Frequency = COUNTROWS(FILTER('Corrected', 'Corrected'[In total, how many times did you serve/act in this capacity?] = MAX('Corrected'[In total, how many times did you serve/act in this capacity?])))
  
  Note: column name in my data is called "In total, how many times did you serve/act in this capacity?", as such, I updated "Occurence" from your code to work with mine.
  

2. Total Count Measure:

• Total Occurrences = COUNTROWS('Corrected')

3. Percenage Measure:

• Percentage = DIVIDE([Frequency], [Total Occurrences], 0) * 100

[samuelhoward]

Update: I figured out  I need to use the Matrix table, and NOT "table". When I put inthe three DAX measures you stated, my percentage is not working as they are all listed out as 100.00. Any thoughts? Can you check the code I am using, as I think the issue might be in there:

1. Frequency Measure: 

• Frequency = COUNTROWS(FILTER('Corrected', 'Corrected'[In total, how many times did you serve/act in this capacity?] = MAX('Corrected'[In total, how many times did you serve/act in this capacity?])))
  
  Note: column name in my data is called "In total, how many times did you serve/act in this capacity?", as such, I updated "Occurence" from your code to work with mine.
  

2. Total Count Measure:

• Total Occurrences = COUNTROWS('Corrected')

3. Percenage Measure:

• Percentage = DIVIDE([Frequency], [Total Occurrences], 0) * 100

[Anonymous]

Hi @showard40k ,

I think you can also create a new table to achieve your requirements.

Firstly I create a table as you mentioned.

Then I think you can create a new table and try this DAX code.

NewTable =
VAR TotalCount =
    COUNTROWS ( 'Corrected' )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( 'Corrected', 'Corrected'[Occurrence] ),
        "Frequency",
            COUNTX (
                FILTER (
                    'Corrected',
                    'Corrected'[Occurrence] = EARLIER ( 'Corrected'[Occurrence] )
                ),
                'Corrected'[Occurrence]
            ),
        "Percentage",
            FORMAT (
                COUNTX (
                    FILTER (
                        'Corrected',
                        'Corrected'[Occurrence] = EARLIER ( 'Corrected'[Occurrence] )
                    ),
                    'Corrected'[Occurrence]
                ) / TotalCount,
                "0.00%"
            )
    )

Best Regards

Yilong Zhou

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

[samuelhoward]

Hi v-yilong-msft

Unfortunately, I get the following error when using your code. Any thoughts?

[Ashish_Mathur · Accepted solution]

Hi,

These measures work

Measure = COUNTROWS(Corrected)

Measure 2 = CALCULATE([Measure],ALL(Corrected[Occurrence]))

Measure 3 = DIVIDE([Measure],[Measure 2])

Hope this helps.

[showard40k]

Once I have the three measure created (Measure, Measure 2 and Measure 3) how do I create the table and place the measures in to look like yours?

[Ashish_Mathur]

Drag and drop

[dharmendars007]

Hello @showard40k 

To calculate the frequency and percentage of each occurrence in Power BI, based on your data from the "Corrected" table, you can create a few DAX measures like below

1. Frequency Measure

Frequency =
COUNTROWS(FILTER('Corrected', 'Corrected'[Occurrence] = MAX('Corrected'[Occurrence])))

2. Total Count Measure

Total Occurrences = COUNTROWS('Corrected')

3. Percenage Measure

Percentage = DIVIDE([Frequency], [Total Occurrences], 0) * 100

Create a Table Visual:

1. Add the Occurrence column from the Corrected table.
2. Use the three DAX measures (Frequency, Total Occurrences, and Percentage) in the visual.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[showard40k]

Hi Dharmendar, I completed the measures, but not sure how to put them into a table. When I do, I get the following, which does not look right. Note, in my data I have 10,000 rows, so its going to be different result than the example I gave above: