Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Displaying grand total on every line

‎05-21-2019 09:07 AM

I know I have fixed this issue before, but I am having a brain-dead morning and need help from one of you brilliant minds.  I have a customer fact table and a sales table.  The tables have an indirect relationship between customer id. Customer Name in the matrix is from the Customers fact table.  I need to total the sales by customer in a matrix, but only completed sales.  I added the column:

 

Completed Sales = IF([Sale Complete]="Yes", [Quantity], 0)

 

In my matrix, the grand total is displaying on every row, rather than the customer subtotals.  Below is a mock-up.  What am I doing wrong?

 

mockup.JPG

Labels:
Message 1 of 11
2,771 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to Anonymous

‎05-21-2019 09:41 AM

@Anonymous -

Ah, right - at first, I also thought, as @edhans  mentioned, that Quantity was a measure, because of the way you wrote it initially. Below, the measure is updated. 

Regarding Project Manager, I'm guessing that it also an Inactive Relationship? If so, you can apply the same pattern with USERELATIONSHIP.

Completed Sales = CALCULATE(
    SUM(Sales[Quantity]),
    USERELATIONSHIP(Sales[Customer Id],'Customer'[Customer Id]),
    Sales[Sale Complete]="Yes"
)

Cheers,

Nathan

View solution in original post

Message 10 of 11
2,734 Views
0
10 REPLIES 10

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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.

View All