cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Displaying grand total on every line

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
natelpeterson New Contributor
New Contributor

Re: Displaying grand total on every line

@StaceyMcCain -

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

10 REPLIES 10
edhans New Contributor
New Contributor

Re: Displaying grand total on every line

What is your Sale Completed measure? It seems to have an ALL() inside of it.

natelpeterson New Contributor
New Contributor

Re: Displaying grand total on every line

@StaceyMcCain - Could you share a screenshot of your model diagram?

Re: Displaying grand total on every line

@natelpeterson  I'm trying to avoid sharing confidential information which is why I put a pic of a mockup of the data.  My actual model has more tables which is why the relationship between these two is inactive.

natelpeterson New Contributor
New Contributor

Re: Displaying grand total on every line

@StaceyMcCain  - You can activate the relationship in your Measure like this:

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

Re: Displaying grand total on every line

@edhans There is not a measure here, it is a column:  Completed Sales = IF([Sale Complete]="Yes",[Quantity],0)

natelpeterson New Contributor
New Contributor

Re: Displaying grand total on every line

@StaceyMcCain  - This would actually be a bit better:

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

Re: Displaying grand total on every line

Ok. When referencing a table, always use the table name. Table[Column]. Then it is clear it isn't a measure. Measures are referenced with just [], so [Sales Completed] looks like a measure, not a table/column reference. The recommendation by @natelpeterson is probably correct - you need USERELATIONSHIP() to activate the relationship and get it to report properly, but without seeing more of the model, it is hard to know.

 

If it works, great.

 

If not, mock up a fake model and post for us to look at.

Re: Displaying grand total on every line

@natelpetersonI had to throw a SUM in after CALCULATE or it errored.  However it is still listing the grand total on each Project Manager line, rather than their subtotal.

Highlighted
natelpeterson New Contributor
New Contributor

Re: Displaying grand total on every line

@StaceyMcCain -

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