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

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

@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 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.
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 Employee

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

Thanks in advance

Microsoft Employee

@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

## Helpful resources

Announcements

#### 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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors