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.
Hi
I have a simple datamodel with date, store, customer, product and a sales table binding them together.
I need to to do 3 mesaures that I am struggleing with.
1) The total sales of customers visiting Store A that have not bought anything in any other store.
2) The total sales of customers visiting Store B that have not bought anything in any other store.
3) The total sales of customers visiting BOTH Store A and B
I need to measure this over the date dimension
Thanks
Solved! Go to Solution.
@Anonymous,
Check the following measures.
TotalSalesA = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( VALUES ( Sales[CustomerId] ), CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 1 && CALCULATE ( MAX ( Sales[StoreId] ) ) = 1 ) )
TotalSalesAB = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( VALUES ( Sales[CustomerId] ), CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ), Sales[StoreId] IN { 1, 2 } ) = 2 ) )
The TotalSalesAB had a bug if a customer visits stores 1, 2 and 3 for example. So i changed it to this:
TotalSalesAB = CALCULATE ( [TotalSales]; FILTER ( VALUES ( Sales[CustomerId] ); CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 2 && CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ); Sales[StoreId] <> 1 && Sales[StoreId] <> 2 ) = 0 ) )
Hi @Anonymous,
Share a dataset and show the expected result.
Maybe try something like this?
[Only A Sales] = CALCULATE ( [Sales], FILTER ( VALUES ( Sales[CustomerID] ), DISTINCTCOUNT ( Sales[StoreID] ) = 1 && Sales[StoreID] = "A" ) )
You're simultaneously checking that the customer has only shopped in 1 store (distinctcount = 1) and that this store = A.
Change "A" to "B" for the 2nd measure.
For the 3rd measure, try this:
[A or B Sales] = CALCULATE ( [Sales], FILTER ( VALUES ( Sales[CustomerID] ), DISTINCTCOUNT ( Sales[StoreID] ) = 2 && Sales[StoreID] in {"A", "B"} ) )
Trying this out now in a simplified model.
TotalSalesA = CALCULATE( [Sales], FILTER( VALUES(Sales[CustomerId]), DISTINCTCOUNT(Sales[StoreId]) = 1 && Sales[StoreId] = 1 ) )
Getting error:
A single value for column 'StoreId' in table Sales cannot be determined. This can happen when a measure formula refers to a column that contains many values withour specifying an aggregation.
@Anonymous,
Check the following measures.
TotalSalesA = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( VALUES ( Sales[CustomerId] ), CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 1 && CALCULATE ( MAX ( Sales[StoreId] ) ) = 1 ) )
TotalSalesAB = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( VALUES ( Sales[CustomerId] ), CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ), Sales[StoreId] IN { 1, 2 } ) = 2 ) )
Thats it! Thankyou so much.
The TotalSalesAB had a bug if a customer visits stores 1, 2 and 3 for example. So i changed it to this:
TotalSalesAB = CALCULATE ( [TotalSales]; FILTER ( VALUES ( Sales[CustomerId] ); CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 2 && CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ); Sales[StoreId] <> 1 && Sales[StoreId] <> 2 ) = 0 ) )
@Anonymous,
If your problem has been resolved, please help mark the useful reply as answer. Your contribution is highly appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |