I'm working on a fact table to deliver a scorecard in PowerPivot. I'm having a problem creating a DAX expression for the calculated column.
I've created a simple version of the problem here. The requirements for the scorecard are to get the sum of order quantity filtered by customer or location. Multiple customers and multiple locations can be included in the filter.
There can also be multiple customers and locations for a single order.
Some sample data is here:
OrderNum |
Customer |
Location |
Quantity |
111
|
A |
NY |
3 |
111
|
A |
LA |
3 |
222 |
B |
FL |
5 |
222 |
C |
FL |
5 |
222 |
D |
FL |
5 |
333 |
E |
NY |
7 |
333 |
E |
FL |
7 |
333 |
F |
NY |
7 |
333 |
F |
FL |
7
|
The following TSQL query will get the correct data:
WITH DistinctQuantity
AS ( SELECT
s.OrderNum
,s.Quantity
FROM
dbo.Scorecard s
GROUP BY
s.OrderNum
,s.Quantity
)
SELECT
dq.OrderNum
,SUM(dq.Quantity)
FROM
DistinctQuantity dq
GROUP BY
dq.OrderNum
WHERE clauses can be added to the query to filter for multiple customers and locations and it will always return the correct data.
I'd like to create this in PowerPivot using DAX, but I can't find a way to get DISTINCT() or VALUES() to return the unique values for multiple fields.