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?Occurrence1234512345I want it to change to this:OccurrenceFrequencyPercentage1220%2220%3220%4220%5220%Total10100%

Hi, These measures work Measure = COUNTROWS(Corrected)Measure 2 = CALCULATE([Measure],ALL(Corrected[Occurrence]))Measure 3 = DIVIDE([Measure],[Measure 2]) Hope this helps.

Unique number column | Microsoft Fabric Community

9 Replies

dharmendars007
Memorable Member
1 year ago
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:
Add the Occurrence column from the Corrected table.
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
  Regular Visitor
  1 year ago
  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:
Ashish_Mathur
Super User
1 year ago
Hi,

These measures work

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

Hope this helps.
- showard40k
  Regular Visitor
  1 year ago
  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
    Super User
    1 year ago
    Drag and drop

Anonymous

Not applicable

1 year ago

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.

Unique number column.pbix22 KB

samuelhoward
Frequent Visitor
1 year ago
Hi v-yilong-msft

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

samuelhoward
Frequent Visitor
1 year ago
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
Frequent Visitor
1 year ago
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

Forum Discussion

Unique number column

9 Replies

Create a Table Visual:

Related content

Unique Count in Row convert

Will Unique include the null's count in column profiling in Power Query Editor?

Sequencing Numbering within a Group Based off Unique Values

Copy unique Numbers

unique value in measure column

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