cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

how to calculate total count and total by group

Hi,

I have integrated sharepoint list which has log entry and has following columns

 

S-NO
Created By
Company Name
Time In
Time Out

 

i want to calulate data like

 

Total records : 10
Company name have most entry : ABC Company
Created By have most entry : ABC Person


Please guide how can i calculate this.
Thx

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: how to calculate total count and total by group

Hi @imughal

Here is a modified pbix according to your dataset.

 

 

Best Regards

Maggie

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: how to calculate total count and total by group

Hi

How do you define “Company name have most entry”?

 

The company name which has most counts of rows in the column “Company name”?

 2.png

 

Or, The company name which have the most time period between Time in and Time Out?

 3.png

 

If situation1, I create measures to achieve results below

 4.png

totalrecords = CALCULATE(COUNTROWS(Sheet1),ALL(Sheet1))

 

countpercompany = CALCULATE(COUNT(Sheet1[S-NO]),ALLEXCEPT(Sheet1,Sheet1[Company Name]))

rankofcount = RANKX(ALL(Sheet1),[countpercompany],,DESC)

Company name have most entry = IF([rankofcount]=1,[Company Name],BLANK())

 

countpercreatedby = CALCULATE(COUNT(Sheet1[S-NO]),ALLEXCEPT(Sheet1,Sheet1[Created By]))

rankofcount2 = RANKX(ALL(Sheet1),[countpercreatedby],,DESC)

created by have most entry = IF([rankofcount2]=1,MAX([Created By]),BLANK())

 

If situation2,

Create measures below to get Company name which has most entry

5.png

Measure1 = DATEDIFF(MAX([Time In]),MAX([Time Out]),MINUTE)

Measure2 = RANKX(ALL(Table1),[Measure1],,DESC)

Measure 3 = IF([Measure2]=1,MAX([Company Name]))

 

Best Regards

Maggie

Highlighted
Helper II
Helper II

Re: how to calculate total count and total by group

thx for quires, but how i do it in Power Bi.

 

Highlighted
Community Support
Community Support

Re: how to calculate total count and total by group

Hi @imughal

Here is my pbix.

 

Best Regards

Maggie

Highlighted
Helper II
Helper II

Re: how to calculate total count and total by group

Hi,

now i got where to create measure. basically i want following result. pls guide which formula to use.

 

SNOCompany 
1ABC 
2RR 
3FF 
4ABC 
5RR 
6ABC 
7FA 
8XY 
9ABC 
10ABC 
   
OutPutCompany NameCount
1ABC5

thx

Highlighted
Helper II
Helper II

Re: how to calculate total count and total by group

It show multiple entry for company where i want to show one entry with just count.

 

Thx

Highlighted
Community Support
Community Support

Re: how to calculate total count and total by group

Hi @imughal

Here is a modified pbix according to your dataset.

 

 

Best Regards

Maggie

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors