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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tbucki1
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
Cmcmahan
Resident Rockstar
Resident Rockstar

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!

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

Anonymous
Not applicable

[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.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors