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!
can anyone help me on this?
I have TABLE A with accounts'data like this one:
Account name | Account created at Date (week num.-year) |
Alex | 3-2020 |
Max | 5-2020 |
Neil | 9-2020 |
and TABLE B with orders' data like this one:
Account name | Weekly Spent | Order date (week num.-year) |
Alex | £5 | 3-2020 |
Alex | £10 | 5-2020 |
Max | £20 | 6-2020 |
Neil | £20 | 9-2020 |
These tables are connected by the "account name" field.
I want to get the "weekly spent"/"cumulative number of customers". This should be the result:
Order date (week num.-year) | 3-2020 | 5-2020 | 6-2020 | 9-2020 |
"weekly spent"/"cumulative number of customers" | (£5/1) = £5 | (£10/2)=£5 | (£20/2)=£10 | (£20/3)=£6.6 |
I've already calculated the "cumulative number of customers", but when I create the matrix visualization, I'm unable to specificy to divide "weekly spent" from TABLE A by "Cumulative num of customers" from TABLE B, where "account created date" from TABLE A = "order date" from TABLE B.
These 2 tables are already connected by "account name" and I'm unable to create a second connection between "order date" and "account created at date".
I hope I was clear.
Thanks"
Solved! Go to Solution.
@Anonymous
You need to create a datetime table and create the relationships like the screenshot below.
Then you create a measure to calculate the reslut
result =
VAR maxdate = max('datetime'[Date])
VAR accumalated = CALCULATE(sum(TableB[Weekly Spent]),FILTER(TableB,'TableB'[Order date (week num.-year)]<=maxdate))
VAR number=CALCULATE(COUNT('TableA'[Account name]),FILTER(all('TableA'),'TableA'[Account created at Date (week num.-year)]<=maxdate))
Return accumalated/number
At last, use the date column from datetime table and the measure you created to show the result that you want.
Proud to be a Super User!
you need a Dates table in your data model. Once you have that you can construct the required filters.
When does you cumulative customer count start? does it reset every year?
Hi!
a Dates table to connect to Table A and Table B? How do I connect it?
cumulative customer count start from the beginning. No need to reset.
Thanks for your reply
@Anonymous
You need to create a datetime table and create the relationships like the screenshot below.
Then you create a measure to calculate the reslut
result =
VAR maxdate = max('datetime'[Date])
VAR accumalated = CALCULATE(sum(TableB[Weekly Spent]),FILTER(TableB,'TableB'[Order date (week num.-year)]<=maxdate))
VAR number=CALCULATE(COUNT('TableA'[Account name]),FILTER(all('TableA'),'TableA'[Account created at Date (week num.-year)]<=maxdate))
Return accumalated/number
At last, use the date column from datetime table and the measure you created to show the result that you want.
Proud to be a Super User!
Hi,
thanks for your reply. Table A and Table B are already connected by "account name" and this doesn't allow me to build relationships you showed me and keep them both active.
Hi @Anonymous
Based on the solution provided by @ryan_mayu If you'd like to both relationship, you can active one of them and use USERALATIONBSHIP to enable the another one.
sorry I'm new to Power BI and I struggle to understand the solution. I want to keep both relationships active. Where do I insert the USERLATIONSHIP formula?
@Anonymous
Please see the doc file of userelationship.
https://docs.microsoft.com/en-us/dax/userelationship-function-dax
Proud to be a Super User!
@Anonymous
you need to delete the existing relationship and rebuild new relationships.
Proud to be a Super User!
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 |