cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agsom Frequent Visitor
Frequent Visitor

countif in Power BI

Hi! Pls help as I can not get through this.

I have this data in 'H1 raw data final' table

IDDomainShortmonthDarab (piece)
1ITjan1
2ITfebr1
3NWjan1
4NWfebr1
5ITfebr1
6NWfebr1
7ITfebr1
8ITjan1
9ITfebr1
10NWjan1
11ITfebr1

And would like to create a new table 'Monthlygroupby' like this:

MonthITNWTotalmonth
jan224
febr527

 

I have started a new table and managed to create a table with totalmonth, but I do not manage to put in the filtered IT and NW data.

Monthlygroupby = GROUPBY('H1 raw data final';'H1 raw data final'[Shortmonth];"totalmonth";SUMX(CURRENTGROUP();'H1 raw data final'[Darab]))

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: countif in Power BI

Hi @agsom ,

Please see the pictures below. I created a short version of your table.  Then dropped the data onto a matrix visualization.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Domains.PNG

 

Domains3.PNG

 

 

View solution in original post

4 REPLIES 4
Nathaniel_C Super Contributor
Super Contributor

Re: countif in Power BI

Hi @agsom ,

Please see the pictures below. I created a short version of your table.  Then dropped the data onto a matrix visualization.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Domains.PNG

 

Domains3.PNG

 

 

View solution in original post

Nathaniel_C Super Contributor
Super Contributor

Re: countif in Power BI

Hi @agsom ,

However if you wish to create a new table, and not a visualization, the easist way is to go back to your original query, rename it. Then

delete the columns you don't want in the new table, (like ID) move the month column and rename it. Then pivot the columns.

Added a picture showing the pivot step. Highlight the Domains column (in my table) and click on Pivot Columns, choose the values I have selected.

 

Finally add a column called TotalMonth with the totals of the two domain columns.

See my picture below for the code using Adv Editor as well as the final table. If you refresh, this new table will be refreshed as well.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

domains2 master.PNG

 

Pivot Column.PNG

 

 

Domains2 Table.PNG

agsom Frequent Visitor
Frequent Visitor

Re: countif in Power BI

Hi @Nathaniel_C ! Thank you very much for putting effort into my small thing 🙂

I will try to make it only with visualization, as I see creating a table requires more knowledge.

I would have one more question. Actually I would like to make visualization out of this: showing the monhtly average / domain.

Here is the matrix (numbers are different):

SnipImage.JPG

 

 

 

 

 

 

And I would like to create some visualization like this, but showing the avg per domain (IT, NW). I could do the total avg with a measure like this:

cardpowerbi.png

 

 

 

 

 

 

Shall I make measures or is there an easier way to show the avg numbers per domain? Best would be to have a treemap but not with totals but with avg. This is with totals, I could not find how to turn it into avg:

 

tree.PNG

Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: countif in Power BI

Hi @agsom ,

 

That one is easy, as you have already done the hardwork.  Simply go to where you have dropped the columns into the values on the tree visualization. Click the down arrow and select Average. Your tree will be as you desire it to be. See picture below for clarification.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

Average of domains.PNG

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)