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

Distinct row count amount at subtotal level not working

I am trying to perform a quick calculation of the number of row count, however, the formula is counting all rows even rows not visible when the table/pivot is collapsed. When expanded I can see it only count the rows there. Below is my DAX formula that I am going to need help with.

 

Number of EOs:=IF(ISBLANK('Sales - Actuals'[Sales - 16 Total Operating Income]),BLANK(),CALCULATE(DISTINCTCOUNT('Sales - Actuals'[EmpID]),'Revenue Generators'[Title Category - RG]="EO"))

 

Here is my pivot. Hard to see but it is quite simple… I have a bunch of people grouped by region (East for example)… First value column is the income per person in 2019, the second column is a count. You can’t see it but if you add up all the 1’s in the “Number of EO” column it totals to 207. The is the number of visible rows of people with income amounts.

 

Capture1.jpg

 

When I collapse all the regions I see the total for East was $50.1M which is correct and is the value I would get above if I summed all 207 income rows…. HOWEVER, I see a count of 244 which is not the correct number of individuals with income. It is counting all individuals in the East, even those with no income. How can I adjust my formula so that the 244 actually shows 207? I plan to use these two values (Income and count) to perform another average calc Income/EO, but I need to get the denominator value correct first.

 

Capture2.JPG

4 REPLIES 4
Super User III
Super User III

Re: Distinct row count amount at subtotal level not working

Sure. I'm guessing there are 37 people where [Sales - 16 Total Operating Income] is 0?

 

You just need to add in a case for if the Operating Income is zero.

Number of EOs:=IF(ISBLANK('Sales - Actuals'[Sales - 16 Total Operating Income]) || SUM('Sales - Actuals'[Sales - 16 Total Operating Income] = 0,BLANK(),CALCULATE(DISTINCTCOUNT('Sales - Actuals'[EmpID]),'Revenue Generators'[Title Category - RG]="EO"))

It might be easier if you create a smaller test region with about a dozen employees, representing each type of case (blank income, positive income, negative income, and zero income among others) and make sure your numbers there work out before applying the same calculation to the large dataset.   207-244 rows is a lot to work with and debug!

tbucki1 Frequent Visitor
Frequent Visitor

Re: Distinct row count amount at subtotal level not working

Yes, you are correct. If you count the people with null income, it would be 37 people. I tested the formula you sent over, however it is still showing 244. Also, Dixon is not counted but Eales is? Row count should be 207, not 244.

 

Capture.JPG

Super User III
Super User III

Re: Distinct row count amount at subtotal level not working

Huh, that's weird. Does Eales have both positive and negative incomes that sum up to zero?

 

Could you share an example dataset that demonstrates the problem?  Even if you had to scrub the data of personal info or create a smaller amount of fake data it would be helpful. This looks like it might be a deeper data model issue that will be incredibly difficult to diagnose through screenshots on this forum.

Super User IV
Super User IV

Re: Distinct row count amount at subtotal level not working

[Number of EOs] :=
CALCULATE (
	COUNTX (
		VALUES( 'Sales - Actuals'[EmpID] ),
		var __operatingIncome = [Sales - 16 Total Operating Income]
		return
		DIVIDE(
			__operatingIncome,
			__operatingIncome
		)
	),
	KEEPFILTERS( 'Revenue Generators'[Title Category - RG] = "EO" )
)

-- or

[Number of EOs] :=
CALCULATE (
	COUNTX (
		VALUES( 'Sales - Actuals'[EmpID] ),
		var __operatingIncome = [Sales - 16 Total Operating Income]
		return
		DIVIDE(
			__operatingIncome,
			__operatingIncome
		)  
	),
	'Revenue Generators'[Title Category - RG] = "EO"
)

-- depending on how you want this measure to
-- behave when filtering on 'Revenue Generators'[Title Category - RG]

Best

D.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors