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

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

Reply
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.example.png

 

2 ACCEPTED SOLUTIONS
v-jiascu-msft
Employee
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.

View solution in original post

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!

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
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.example.png

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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