Average of a distinct count measure

DAX DAX Calculations
1

Hi,

I have some data of flights completed for an airline that I am analyzing and I have used distinct count to get the daily number of aircraft that are being operated. This all works fine except for on a total basis as the total in the distinct count column is the maximum amount of aircraft operated during the whole data pull rather than the average number of aircraft used in the period.

I have tried a few formula with no success and assume its an averageX type approach but any guidance on how to calculate the period average of a distinct count measure would be fantastic.

Thank you all

*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

  • Your current work-in-progress PBIX file - VERY IMPORTANT
  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

1 Like
2

Hello @Ronan

Thanks for posting your query in Forum…

You can give a try with the below formula, I assuming this formula without PBIX file. therefore you must tweak this based on your column and row name.

Average & Distinct =

VAR _Discount = DistinctCount(Aircraft)

VAR _AvgDiscount = Average(_Discount)

VAR _Condition = If(Hasonefilter(Aircraft), _Discount, _AvgDiscount)

Return
_Condition

Thanks,
Dharmendar S

3

Thank you for this Dharma,

I cannot seem to use Average for a var. When I go to write the second line of the code: VAR _AvgDiscount = Average(_Discount) the average function will not let me populate it with the VAR or any measure for that fact.

Am I missing something on this?

Thanks
Ronan

4

Hello @Ronan

request you to share us sample PBIX file this will help me assist you further…

Thanks,
Dharmendar S

5

Unfortunately I cannot as it is sensitive project information sadly.
However reading some more it appears to me Average function cannot be used on measures or variables and only used on columns

6

Hi @Ronan.
Instead of real data, please provide a small sample dataset and Excel mock-up of your desired results (using that sample dataset) to assist the forum members in their investigations.
Greg

7

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763
see the link above in providing sensitive information in the power bi file

8

Hi @Ronan, we noticed that you have not responded to the request of our community members. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Bumping this post

10

Hello @Ronan it’s been a while since we got a response from you.

Just following up the information the experts requested above so they can help you further.

As mentioned above by our experts, Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Please be reminded that In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Hello @___. Due to the length by which this post has been active, we are tagging it as Solved. Thanks to the contributors of this post. For further questions related to this post, please make a new thread. Feel free to reopen this thread if you anyone would like to answer the pending inquiry above.