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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
quentin_vigne
Solution Sage
Solution Sage

Number of occurences in a column

Hello ! 

 

I'm trying to fill a Card Box in Power BI with the differents occurences of words in my column

I have a column (NumberTel) that count the number of phone I have in my table (0, 1, 2, 3) and I would like to know how to put (in percent) those values in a Visual element

 

I know how to make a graph (doughnut / circular /etc.) but I would like to put them in a Card box 

 

I know the syntax of this query in SQLServer but can't figure out how to do it in PowerBI...

I don't want to create 4 columns for my 4 values with 1 or 0 in it and them sum it to have the amount of each one (Already had this idea but not the best option)

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION

@quentin_vigne,

Create the following measure in your table.

percentage = COUNTROWS('sNova VUE_Analytique_ClientPerf_Creance')/CALCULATE(COUNTROWS('sNova VUE_Analytique_ClientPerf_Creance');ALL('sNova VUE_Analytique_ClientPerf_Creance'))

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@quentin_vigne,

Could you please share sample data of your table and post expected result here?

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-yuezhe-msft

 

Here's a sample

As you see, there is 3 fields : Telephone1,2 and 3

 

I have a column where I count how many phone I have (Lots of If / And / Or) 

Now what I want is to know how many times I have 3 phones, 2 phones, 1 phone or no phone number. If I put it in a circular graph, everything works and I see the percent of each value 

But when I want to visualize it in a Card box, nothing's appear

@quentin_vigne,

Please check the following blog about how to post example data and expected result.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Hello, 

I didn't know about this, here a sample of my data i've extracted from my database. Name of the table is 'sNova VUE_Analytique_ClientPerf_Creance'

IdDebCreance;IdDebGeneral;IdClientCpte;Telephone1;Telephone2;Telephone3
3180295;2023437;15647;98419543;2388748;66396323
3180321;2023463;15647;23730715;9653883;
3180372;2023514;15647;;98304493;65058057
3180905;2024047;15647;;25400960;
3180909;2024051;15647;38971884;28250141;
3180997;2024139;15647;;25402297;
3181081;2024223;15647;24875270;24875250;
3181189;2024331;15647;96103762;25447529;
3181676;2024818;15647;23863934;25496311;
3181760;2024902;15647;24730040;97533837;68683578
3181777;2024919;15647;62380668;24870794;
3181841;2024983;15647;;23833060;
3181894;2025036;15647;23883559;14322782;
3182050;2025192;15648;;23229679;
3182059;2025201;15648;23344485;23320804;
3182578;2025720;15648;;68572220;67513120
3182586;2025728;15648;;98127766;69892216
3182603;2025745;15648;;95334564;
3182620;2025762;15648;23291068;29744179;
3182686;2025828;15648;;35734364;
3182864;2026006;15648;;60268227;68546469
3183295;2026437;15648;61968856;97351655;
3183339;2026481;15648;23372667;23351790;
3183382;2026524;15648;97158582;23343943;
3183439;2026581;15648;;23262239;

 

 

On PowerBI I add this formula in a new column of the same table (It count the amount of phone number I have in each line :  

 

NombreTel = IF(AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone1]<>"";AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]<>"";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]<>""));"3 Tel";IF(AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone1]="";AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]="";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]=""));"Pas de Tel";IF(AND(OR('sNova VUE_Analytique_ClientPerf_Creance'[Telephone1]<>"";AND(OR('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]<>"";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]<>"");NOT(AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]<>"";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]<>""))));NOT(AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone1]<>"";AND(OR('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]<>"";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]<>"");NOT(AND('sNova VUE_Analytique_ClientPerf_Creance'[Telephone2]<>"";'sNova VUE_Analytique_ClientPerf_Creance'[Telephone3]<>""))))));"1 Tel";"2 Tel")))

Now what I Expect is a card box with multiple results (I don't know the enlighs name for this visualisation) like the one below but this the percentage of each value from my column (How many time do I have 1 Tel, 2 Tel etc...) :

 

 

Thanks in advance

@quentin_vigne,

Create the following measure in your table.

percentage = COUNTROWS('sNova VUE_Analytique_ClientPerf_Creance')/CALCULATE(COUNTROWS('sNova VUE_Analytique_ClientPerf_Creance');ALL('sNova VUE_Analytique_ClientPerf_Creance'))

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Wow ! Thanks a lot, it works perfectly fine !

 

Have a good day,

 

Quentin

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors