topic Re: Best way to obtain percentage of a single column that can have multiple text values in DAX Commands and Tips

Best way to obtain percentage of a single column that can have multiple text values

Dali7 — Mon, 24 Jun 2024 13:02:22 GMT

Hello

I am seeking advice for a basic subject so I do apologise, I have a table that has a list of Users and the results of whether they have looked up a Knowledge Article. The result will either be a Knowledge ref no. or a BLANK. I've been able to create 2 visualisations each with a different filter that provdes the results which is useful. However I can't identify a way to obtain a % from these two figures. Wasn't sure whether I need to create a new column in my table but I'm sure there is an easier way. To help explain I have included below an example of the raw data on the left and how it appears in excel. Basically I am trying to duplicate the pivot table I have in excel on the right in Power BI.

Re: Best way to obtain percentage of a single column that can have multiple text values

Anonymous — Tue, 25 Jun 2024 01:37:28 GMT

Hi Dali7 ,

From the screenshot you provided, the % of KB entry result is intuitively represented as [Count of KB entries] divided by [Count of Blank], which is available in Power BI using the DIVIDE function.

Create a measure:

% of KB entry= DIVIDE(MAX('Table'[Count of KB entry]), MAX('Table'[Count of Blank]), 0)

Compared to counting columns, measure performs better because it doesn't take up memory.

If this syntax doesn't help you out, I would be grateful if you could provide me with the pbix file or sample data.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Best way to obtain percentage of a single column that can have multiple text values

Dali7 — Tue, 25 Jun 2024 07:30:44 GMT

Hi Anonymous

Thank you so much Yang for taking the time to provide a solution, I've been thinking about the answer and I fear I haven't clarified the original data based on the solution provided. Looking at the solution this infers there are 2 columns count of KB entry and count of blank but there is only 1 column in the data on the LHS stated Knowledge Article. Looking at that single column I need to use your solution but based on the cells that contain any text containing KB* and any cells that are blank against each User.

Would I need to create 2 measures first:

a measure that counts the cells in Knowledge Article column for that User that contain KB
a measure that counts the cells in Knowledge Article column for that User that are BLANK

I was able to create a measure but it provided a total of the two variables above rather than a total for each User using the following measures for they only provide a total for the enitre single column:

KB Total of KB entries = count('Data Apr'[Knowledge Article])

KB Field empty = COUNTBLANK('Data Apr'[Knowledge Article])

Once I have those two measures can I then use your original solution to run a calulation on those measures rather than the solution that perfroms a calculation based on 2 columns, would that work?

Any further help with this would be very much appreciated this is my first report.

Re: Best way to obtain percentage of a single column that can have multiple text values

Anonymous — Tue, 02 Jul 2024 04:21:36 GMT

Hi Dali7 ,

Yes, you can certainly do that. Here's a simple example I created based on your needs:

First, here's the sample data I created.

User	Konwledge
User1	KB1000
User2
User3
User4	KB1003
User5	KB1001
User1
User2	KB1000
User3
User4	KB1003
User5	KB1004

I created a calculated column to identify the categories:

Type = IF('Table'[Konwledge] = BLANK(),"Blank","KB Entry")

I created 3 measures to count:

Count of Blank = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[User]),'Table'[Type] = "Blank")) Count of KB Entry = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[User]),'Table'[Type] = "KB Entry")) Total = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[User]))

Finally, calculate the percentage:

% of KB entry = FORMAT(DIVIDE('Table'[Count of KB Entry],'Table'[Total]),"Percent")

Using the matrix visual object, it is displayed as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

Re: Best way to obtain percentage of a single column that can have multiple text values

Dali7 — Tue, 02 Jul 2024 07:41:31 GMT

Hi @v-huijiey-msft

Thanks so much for taking the time to reply, that is exactly what I was looking for and works perfectly. Also I can use this methodology for my other reports. Thanks again for all yout help