Count values from multiple fields

[Anonymous]

Dear Experts,

I want to count values in multiple fields as shown in below image -

I want result like below -

Entry Planted Name	No of Count
P3550	4
M2020	11
M2011	8

Request you to help me how to achieve this in powerbi.

Thanks in advance.

[Sujit_Thakur · Accepted solution]

Dear @bhuprakashs

I have your problem solved, here it is,

But let me mention, you'll need an index table, so that you can relate it to the unpivoted column called as values.
So that counting values other than the index to add in the rows field.

I hope this solves your problem.

I expect a "like in both of my post", will motivate me to help more friends like you.
and if this post brought answers to your questions please accept this post as a solution

If there are further doubts please let me know.

Best regards

Thakur Sujit

[Sujit_Thakur]

Dear @Anonymous ,
I got your question 
https://drive.google.com/file/d/1msJ8bR2IuJfercsDW6D3etuQ70OjHvJZ/view?usp=sharing

go to this link download , it is yur solutiuon 

steps are 
1) You must have a index table with Distinct name 
2)  go in transform data , using home option in your Head ribbon , after going in transform , you will see your table
3)Now select all columns you want to count , then go in transform tab click unpivot selected columns 

3)Then close and apply .

4) Make sure your name from index has relation with values after unpivoting .(using model ribbon on LHS)

5) then just put


Done !!!

Please dont forget to give kudos ,
If it help you reach your solution please accept this post as solution .
if any further doubt please let me know ill be more than happy to help

Thanks 
Sujit 

[Anonymous]

HI @Sujit_Thakur ,

My desired output is bit different. Attaching the dummy data and desired output in excel.

Kindly have a look and let me know in case of any query.

https://drive.google.com/file/d/1KAVuHoTz2TeEsnyCxBeipLsr2YNbgw7-/view?usp=sharing

[Sujit_Thakur · Accepted solution]

Dear @bhuprakashs

I have your problem solved, here it is,

But let me mention, you'll need an index table, so that you can relate it to the unpivoted column called as values.
So that counting values other than the index to add in the rows field.

I hope this solves your problem.

I expect a "like in both of my post", will motivate me to help more friends like you.
and if this post brought answers to your questions please accept this post as a solution

If there are further doubts please let me know.

Best regards

Thakur Sujit

[Anonymous]

Thank you @Sujit_Thakur for your help.

I have done it. I just did unpivoting and then grouped it and it solved my problem.

Thank you all once again 🙂

[Sujit_Thakur · Accepted solution]

Dear @bhuprakashs ,
happy to know , if you have any doubt you can always ask ,
just drop me a message here in community message box ,
ill be happy to help.

Regards
Sujit Thakur

[Anonymous]

Dear All,

Thanks for your quick revert on my query.

Let me try it my end and come back to you as it's looking bit difficult for me but I will try it.

Regards,

Bhu

[Sujit_Thakur]

@Anonymous 

What you can do is download file from drive link which I shared in my last post it will be a quick reference

[amitchandak]

@Anonymous , better to unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Or create a join it with a common dimension Entry planned name, all 4, With one active and another inactive. Create 4 count measure with userelation and them

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

How to combine value and create table. example in pbix . you have to do for four column

https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

[Sujit_Thakur]

@Anonymous  ,
Can you let me know that Entry planted name are different columns so do you want such results ??

Name   entry planted 1     entry planted 2      entry planted 3

A                 12                           14                            15 
B                   3                             12                          12             
C                    23                            1                              123

Let me know , so that i could help

[mahoneypat]

Your data should be unpivoted in the query editor (highlight the columns not shown, right click and choose Unpivot Other Columns).  Having all your data in a single column will make your analysis much easier (you would still have the Name # in the "Attribute" column for other analysis).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@Anonymous - You can UNION your columns together and then FILTER out the values that match your entry plant name and then COUNTROWS the filtered set.