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'm trying to create a measure for calculating a sum of first-time sales. This is what my schema looks like.
Currently I have this measure,
// Get the sum of sales quantity for the date equal to the first day of sales
[FirstSaleAmount] =
CALCULATE(SUM(Sales[SalesQty]; FILTER(Sales; MIN(Sales[Date]) = FIRSTNONBLANK(VALUES(Sales[Date]); 1)))
which works only when sales data is sliced by both client and product. Any idea how should I modify it to get a valid sum over all clients?
Thanks
Solved! Go to Solution.
Hi @tux117,
From whar I've read about SUMMARIZE it must be possible to incorporate it into a measure use it in a filter without creating a "physical" table, but I didn't manage to do that.
Yes, you don't need to create the physical summarize table in this scenario. Based on my test, you should be able to use the formula below to create the calculate column without the physical table.
IsFirstSale = CONTAINS ( SUMMARIZE ( Sales; Sales[Client]; Sales[Product]; "FirstSaleDate"; MIN ( Sales[Date] ) ); [Client]; Sales[Client]; [Product]; Sales[Product]; [FirstSaleDate]; Sales[Date] )
Regards
Hi @tux117,
Could you try the formula below to see if it works in your scenario?
[FirstSaleAmount] = SUMX ( Client; SUMX ( Product; CALCULATE ( SUM ( Sales[SalesQty] ); FILTER ( Sales; Sales[Date] = FIRSTNONBLANK ( VALUES ( Sales[Date] ); 1 ) ) ) ) )
Regards
Hmmm, it seems my reply has disappeared, so I'll have to write it once more.
@v-ljerr-msft unfortunately it does not work, the values in the table are right, when sliced by product and client, but overall result makes no sense.
I managed to do it the other way by creating a separate table containing dates of first sales
FirstTransactions = SUMMARIZE ( Sales; Sales[Client]; Sales[Product]; "FirstSaleDate"; MIN ( Sales[Date] ) )
and then using it to create a custom column in the main table which shows if the record corresponds to the first sale,
IsFirstSale = CONTAINS( FirstTransactions; FirstTransactions[Client]; Sales[Client]; FirstTransactions[Product]; Sales[Product]; FirstTransactions[FirstSaleDate]; Sales[Date] )
The it's possible to use this column to filter
FirstSaleAmount2 = CALCULATE( SUM(Sales[SalesQty]); Sales[IsFirstSale] = TRUE() )
It looks pretty ugly but still works. From whar I've read about SUMMARIZE it must be possible to incorporate it into a measure use it in a filter without creating a "physical" table, but I didn't manage to do that.
Hi @tux117,
From whar I've read about SUMMARIZE it must be possible to incorporate it into a measure use it in a filter without creating a "physical" table, but I didn't manage to do that.
Yes, you don't need to create the physical summarize table in this scenario. Based on my test, you should be able to use the formula below to create the calculate column without the physical table.
IsFirstSale = CONTAINS ( SUMMARIZE ( Sales; Sales[Client]; Sales[Product]; "FirstSaleDate"; MIN ( Sales[Date] ) ); [Client]; Sales[Client]; [Product]; Sales[Product]; [FirstSaleDate]; Sales[Date] )
Regards
.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |