Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Nathaniel_C
Super User
Super User

Hi @Anonymous ,

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Nathaniel_C
Super User
Super User

Hi @Anonymous ,

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

Hi @Anonymous ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Anonymous ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors