cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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)

1 ACCEPTED SOLUTION
Microsoft

@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

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.
6 REPLIES 6
Microsoft

@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.
Solution Sage

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

Microsoft

@quentin_vigne,

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

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.
Solution Sage

@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...) :

Microsoft

@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

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.
Solution Sage

@v-yuezhe-msft

Wow ! Thanks a lot, it works perfectly fine !

Have a good day,

Quentin