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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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

10 REPLIES 10
Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous  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.

Anonymous
Not applicable

@Anonymous  - 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])
)
Anonymous
Not applicable

@Anonymous  - This would actually be a bit better:

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

@AnonymousI 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.

Anonymous
Not applicable

@AnonymousI used the same formula, but as a measure, and now it is calculating correctly.  

Anonymous
Not applicable

@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

edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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 @Anonymous 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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