Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AgoZ_KH
Resolver I
Resolver I

Calculate percentage of a txt column of a table with the grabnd total of another one table

Hi Community! hoping in a hint or help to my issue.

I have a text column with some users valutations like i.e.(Very Good,Good, Acceptable, Poor, Very Poor). Not all the rows have a user valutations. Of course I can easly count the percentage of every value to obtain a table like this:
VAL                      Num         %
Very Good             98        4,59%
Good                   833       39,03%
Acceptable          594       27,84%
Poor                    138         6,47%
Very Poor            471       22,07%
                          2134     100,00%

 

But my problem is not to calculate the percentage based on the Number of valutations expressed, BUT calculate it on all the response including who have not answered.

Infact in the first case I have at the numerator the sum of the categorized value, and at denominator the sum of all valutations i.e.:
98/2134, 833/2134 and so on.

BUT total number of the row of my table is really equally to the number: 63439. So my problem is to put at the denominator the total sum of the rows.

98/63439, 833/63439 and so on ...

For simplify my work I have created two separate tables: Tab_A with only all the users valutions, and Tab_B with all the answer provided by the users (NOTE: I haven't only blank values, but others valutations in the same column).

 

So I try a measaure with a simple formula like:

PercentageOnGrandTot =
DIVIDE (
(CALCULATE
COUNT( 'Tab_A'[valutations] ),
CALCULATE (
(COUNT( 'Tab_B'[answers] ),
ALLSELECTED ()
)
)))

 

What I expecting is a table with this values:

VAL                           Num       %
Very Good                  98        0,15%
Good                        833        1,31%
Acceptable               594        0,93%
Poor                         138        0,21%
Very Poor                 471        0,74%
                             63439        3,36%

BUT with the above measure I obtain strange results. Not what I aspected.
Can someone help me to discover the right way of calculation??

Any help will very appreciated!
Thanks in advance
AgoZ

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @AgoZ_KH,

Any update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ryan_mayu
Super User
Super User

pls provide some sample data and expected output





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.