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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors