Divide won't show value even though it's not divide by 0

[Anonymous]

I want to have 3 matricies setup, the first 2 are working perfectly fine, just the third one down is missing some values in columns that should appear (not divide by 0)

1) Row, # of Sales People

    Columns,  3 Contractors , 4 Juniors, 2 Seniors, 1 Executive, 10 Grand Total (formula)

2) Total Sales $

    Columns, $150 Contractor, $400 Junior, $400 Senior, $1000 Executive, $1,950 Grand Total (formula)

3) $ / Sales Person

ERROR: Only $/Sales Person of the Grand Total column appears ($195.00), even though other columns should. Missing average sales of Contractor ($50), Junior ($100), Senior ($200), and Executive ($1000).

I have made the following 3 variables:

Num of Sales People = SUM(datatable[HeadCount])

Total Sales  = SUM(datatable[GrossSales])

Average Sale = DIVIDE([Total Sales].[Num of Sales People])

Even when I do DIVIDE([Total Sales].[Num of Sales People],0), it gives me $0s where you would expect to see $50, $100, $200 etc.

Am I missing something?

Edit: 
I tried adding CALCULATE( to both Num of Sales People and Total Sales thinking it would help the column filters from the Matricies. It didn't work.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

What visual did you use? Since you invoked two measures, they should be in the same context. It could be like below.

Column	# of Sales People	Sales $	Avg Sales / Person
Contractors	3	150	50
Juniors	4	400	100
Seniors	2	400	200
Executive	1	1000	1000
total	10	1950	195

To be honest, your scenario is simple. If you still have questions, please provide a sample.

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

What visual did you use? Since you invoked two measures, they should be in the same context. It could be like below.

Column	# of Sales People	Sales $	Avg Sales / Person
Contractors	3	150	50
Juniors	4	400	100
Seniors	2	400	200
Executive	1	1000	1000
total	10	1950	195

To be honest, your scenario is simple. If you still have questions, please provide a sample.

Best Regards,

Dale

[Anonymous · Accepted solution]

 Hi Dale, 

When I designed it another view to troubleshoot it further, it was because I had 'Juniors/Contractors/etc." column sorted based on a different column "RoleSortOrder".

For some reason, Contractors whom I typically want first, was sometimes sorted as 1 and sometimes sorted as 2.

Once I cleaned up this SortOrder to be 100% consistent, PowerBI combined them apporpriately.

Thanks so much Dale!

[Anonymous]

(Other post was flagged as SPAM incorrectly, sorry to the Moderators for the repost)

I want to have 3 matricies setup, the first 2 are working perfectly fine, just the third one down is missing some values in columns that should appear (not divide by 0)

1) Row, # of Sales People

    Columns,  3 Contractors , 4 Juniors, 2 Seniors, 1 Executive, 10 Grand Total (formula)

2) Total Sales $

    Columns, $150 Contractor, $400 Junior, $400 Senior, $1000 Executive, $1,950 Grand Total (formula)

3) $ / Sales Person

ERROR: Only $/Sales Person of the Grand Total column appears ($195.00), even though other columns should. Missing average sales of Contractor ($50), Junior ($100), Senior ($200), and Executive ($1000).

I have made the following 3 variables:

Num of Sales People = SUM(datatable[HeadCount])

Total Sales  = SUM(datatable[GrossSales])

Average Sale = DIVIDE([Total Sales].[Num of Sales People])

Even when I do DIVIDE([Total Sales].[Num of Sales People],0), it gives me $0s where you would expect to see $50, $100, $200 etc.

Am I missing something?

Edit: 
I tried adding CALCULATE( to both Num of Sales People and Total Sales thinking it would help the column filters from the Matricies. It didn't work.

Here's a quick Excel mockup of what I'd hope the PowerBI to be able to show in 3 separate matricies.