Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I've been going around my dataset for quite some time now and now needs some help.
So I have this dataset that I have created a distinct count column based on the number of apps they use for comms, and I average accross the board.
Here is what it looks like (just a sample):
Account | Medium | MediumPerAccount |
Apple | Radio | 2 |
Apple | TV | 2 |
Tesla | Radio | 3 |
Tesla | TV | 3 |
Tesla | A-track | 3 |
TV | 1 | |
TV | 1 | |
Betamax | 2 | |
VHS | 2 |
So the average measure I created is:
Account | Medium | MediumPerAccount | Average App |
Apple | Radio | 2 | Below |
Apple | TV | 2 | Below |
Tesla | Radio | 3 | Above |
Tesla | TV | 3 | Above |
Tesla | A-track | 3 | Above |
TV | 1 | Below | |
TV | 1 | Below | |
Betamax | 2 | Below | |
VHS | 2 | Below |
The graph will show as
Above = 3
Below = 6
But what I need in that graph is based on the app's Average Rank only:
Above = 1
Below = 3
I hope to get some help, tell me what I made a mistake in the codes, and then creating that graph distinct by app then by Average Rank.
Appreciate the help. I hope I was able to explain it properly
Hi @Nogstai ,
1.
AverageCount = AVERAGEX(VALUES('Table'[Account]), CALCULATE(DISTINCTCOUNT('Table'[Medium])))
This measure will return 1 to each account. So [Average App] calcualted column will return "Above Average".
2.
Average App = IF(2.5>'Chat App'[MediumPerAccount], "Below Average", "Above Average")
This calculated column will return below result.
I think you can distinct count the account to get the result you want.
Count = DISTINCTCOUNT('Chat App'[Account])
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So I tried it again, changing from distinct count which is showing 1's
And the actual count which shown all the rows.
What it looked like is:
Bar Graph Y-axis count:
Above Average - 3 instead of 1
Below Average - 6 instead of 3
Bar Graph Y-axis distinct count:
Above Average - 1
Below Average - 1
Hi Rico,
Thanks for the reply, I adjusted the table as my sample numbers were wrong.
For #2, that was just a test sample number to see if it will get a below/above average column.
Is there a way I can replace the "2.5" with a measure or a column? Should I create an average app column?
For the distinct count:
Count = DISTINCTCOUNT('Chat App'[Account])
When I did this, it just gives me
Above - 1
Below - 1
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |