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.
I have three tables. DateTable, Store-List and Store-Transactions. Date have dates. Store-List have list of all the stores. And Store-Transactions have all the transactions happened in all the stores by date. Now I have two tasks.
Formula for 2 is.
APSD = Transaction-Count/([Total-Days]*[ Unique-Stores-Count])
If we select Date range in PBI from 04/01/2019 – 04/30/2019 we get
Transaction-Count as 93. Which is correct.
APSD as 0.62. It calculates total distinct stores (5 Stores in a date range) and based on that it calculates 93/(30*5) = 0.62.
BUT above calculated APSD is not what I want, Here is why -
Based on Store-List table we can notice that -
So, I want APSD formula to be modified in such a way that it calculates in this way –
APSD = 93/(19*3+8*2+3*4) = 1.09
In conclusion - Two considerations - Formula should adjust for number of days a store was not open in the selected date range. And it must count all the open stores even if does not appear in Store-Transactions table rows (which might happen if a store was open but did not do any transactions in the date range selected).
I am sharing the PBI and data files-
https://drive.google.com/file/d/16gwp8k-nni-CS8pxPU5UIsZI5W8wMhq1/view?usp=sharing
Thanks!
Hi @Anonymous ,
Unfortunately, it failed to download the shared file, would you please upload the sample .pbix file onto onedrive and share the download link here? Do mask sensitive data before sharing.
Best regards,
Yuliana Gu
@v-yulgu-msft I apologize for the inconvenience. Here is the link:
https://1drv.ms/u/s!AqY7gk5JfaPjhhI-Kgtouy8NLFjH
Thanks!
@v-yulgu-msft wrote:Hi @Anonymous ,
Unfortunately, it failed to download the shared file, would you please upload the sample .pbix file onto onedrive and share the download link here? Do mask sensitive data before sharing.
Best regards,
Yuliana Gu
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |