PowerBI groupby with filters

Question

My company has tasked with slicing the information on turnover and to create different graphs.

My source data looks like this: Relevant columns are: Voluntary/Involuntary, Termination Reason, Country, Production, and TermDateKey

I am trying to get counts using different filters on the data. I managed to get the basic monthly total using the formula:

Term Month Count = GROUPBY('Turnover Source','Turnover Source'[TermDateKey],"Turnover Total Count", COUNTX(CURRENTGROUP(),'Turnover Source'[TermDateKey]))

This gave me a new sheet with the counts for each month. Table that shows TermDateKey on Column 1, and Counts on column 2

I am trying to add onto this table by adding counts but using different filters. For example, I am trying to add another column that gives me the monthly count but filtered for 'Turnover Source'[Voluntary/Involuntary]=="Voluntary". Then another column for 'Turnover Source'[Voluntary/Involuntary]=="Involuntary" and so on. I have not found anywhere that shows me how to do this and when I add in the FILTER function it says that GROUPBY(...) can only work on CURRENTGROUP().

Can some one point me to a resource that will give me the solution I need? I am at a loss, thank you all.

Answer 1

It looks like you may not be aware that you don't have to calculate all possible groupings with DAX formulas.

The very nature of Power BI is that you use a column like "Termination Reason" on an X axis or in the legend of a visual. Any measure that you have created on values of another column, for e.g. a count of all rows, will then automatically be calculated to be grouped by the values in "Termination Reason", giving you a count of each of the values in the column.

You do NOT need DAX functions to calculate the grouping values for each measure for each column value combination.

Here is some simple sample data that has been grouped into dates and colours, one chart showing a count of each colour and one chart showing a sum of the Value column. No DAX was written for that.

If your scenario is different, please explain.