cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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.

2 ACCEPTED SOLUTIONS
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

3 REPLIES 3
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors