Number of occurences in a column

[quentin_vigne]

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 🙂

[v-yuezhe-msft]

@quentin_vigne,

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

Regards,

Lydia

[quentin_vigne]

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

[v-yuezhe-msft]

@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

[quentin_vigne]

@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

[v-yuezhe-msft · 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'))

Regards,
Lydia

[quentin_vigne]

@v-yuezhe-msft

Wow ! Thanks a lot, it works perfectly fine !

Have a good day,

Quentin