Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |