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

Getting a count of repetition of the count

Hi!

 

Sorry for the confusing subject. Basically I have two columns, One with POs and the other with Vendors. I do a count of the vendors against teh POs and there are several of them with multiple vendors. What I want to get is the count of the multiple vendors and single vendors against the POs.

For Example:

PO1             Count of Vendor
PO2    2
PO3   3
PO4  3
PO5  3
PO6    1
PO7   1
PO8    1
PO9    1
PO10    1
PO11    1

   What I want to get is the count as to how many times its repated:

Result = 2 is 2 times

              3 is 3 times

              1 is 6 times

 

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Getting a count of repetition of the count

If you want a count in Power Query that returns the counts in a table, group by the count column. Returns this table.

2020-05-12 08_41_40-Untitled - Power Query Editor.png

 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvA3VEAGSjpKRkqxOiAJIzS+MYRrDOWaoPBMUXhmUK2GUL45KtcCTdoSjW9ogC5giBCIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [POs = _t, Counts = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POs", type text}, {"Counts", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Counts"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

If you want it in DAX, simply put your Counts in a table, tell the visual not to aggregate, then use this measure:

 

Count of Counts = COUNTROWS('Table')

 

2020-05-12 08_43_53-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Community Support
Community Support

Re: Getting a count of repetition of the count

Hi @aagni2000 ,

 

You may create measure or column for the Count of Vendor like DAX below.

 

Measure= CALCULATE(COUNT(Table1[Vendors]),FILTER(ALLSELECTED(Table1), Table1[Pos] =MAX(Table1[Pos])))


Column= CALCULATE(COUNT(Table1[Vendors]),FILTER(ALLSELECTED(Table1), Table1[Pos]=EARLIER(Table1[Pos])))

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: Getting a count of repetition of the count

If you want a count in Power Query that returns the counts in a table, group by the count column. Returns this table.

2020-05-12 08_41_40-Untitled - Power Query Editor.png

 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvA3VEAGSjpKRkqxOiAJIzS+MYRrDOWaoPBMUXhmUK2GUL45KtcCTdoSjW9ogC5giBCIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [POs = _t, Counts = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"POs", type text}, {"Counts", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Counts"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

If you want it in DAX, simply put your Counts in a table, tell the visual not to aggregate, then use this measure:

 

Count of Counts = COUNTROWS('Table')

 

2020-05-12 08_43_53-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Community Support
Community Support

Re: Getting a count of repetition of the count

Hi @aagni2000 ,

 

You may create measure or column for the Count of Vendor like DAX below.

 

Measure= CALCULATE(COUNT(Table1[Vendors]),FILTER(ALLSELECTED(Table1), Table1[Pos] =MAX(Table1[Pos])))


Column= CALCULATE(COUNT(Table1[Vendors]),FILTER(ALLSELECTED(Table1), Table1[Pos]=EARLIER(Table1[Pos])))

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors