Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nogstai
Helper I
Helper I

Creating a calculated column based on distinct count (that is based on another column) and average

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):

 

AccountMediumMediumPerAccount

Apple

Radio2
AppleTV2
TeslaRadio3
TeslaTV3
TeslaA-track3
FacebookTV1
FacebookTV1
TwitterBetamax2
TwitterVHS2

 

So the average measure I created is:

AverageCount = AVERAGEX(VALUES('Table'[Account]), CALCULATE(DISTINCTCOUNT('Table'[Medium])))
 
And the column I created I used:
MediumPerAccount = CALCULATE(DISTINCTCOUNT('Table'[Medium]),ALLEXCEPT('Table','Table'[Account]))
 
Now, I have to create a graph, that will determine which accounts medium counts are above or below the average number.
 
I did a basic code for a calculated column:
Average App = IF('Chat App'[AverageCount]>'Chat App'[MediumPerAccount], "Below Average", "Above Average")
And it's showing all "Above Average".
 
Now I think I made a mistake on the average code, but I tested the code and put in a value on the IF, based on the result seen in my average card. Let's say as a sample the average value is 2.5. Then I saw which ones are below and above average.
Average App = IF(2.5>'Chat App'[MediumPerAccount], "Below Average", "Above Average")
 
Now the next problem I have from the tested  is when I'm putting it in a graph, it's counting all the rows. Not as the average against 
 
AccountMediumMediumPerAccount Average App

Apple

Radio2Below
AppleTV2Below
TeslaRadio3Above
TeslaTV3Above
TeslaA-track3Above
FacebookTV1Below
FacebookTV1Below
TwitterBetamax2Below
TwitterVHS2Below

 

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

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1666071870000.png

I think you can distinct count the account to get the result you want.

Count = DISTINCTCOUNT('Chat App'[Account])

RicoZhou_1-1666071991436.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.