Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
@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
@Anonymous - Could you share a screenshot of your model diagram?
@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 - 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 - This would actually be a bit better:
Completed Sales = CALCULATE( Sales[Quantity], USERELATIONSHIP(Sales[Customer Id],'Customer'[Customer Id]), Sales[Sale Complete]="Yes" )
@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.
@AnonymousI used the same formula, but as a measure, and now it is calculating correctly.
@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
What is your Sale Completed measure? It seems to have an ALL() inside of it.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |