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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Average based on distinct count and summing up

Input TableInput TableOutputOutput

Hi everyone,
I have a input table with ID . i need a measure which calculates average of due days foe each Id. In this example my output should be 25. It should sum up the due dates for each ID and then calculate average.when i have a slicer for ID and if i take a single ID it must show sum of due dates(ID 110=15, ID 112=30)
I have tried the following measure
1)m1= averagex(summarize(fi,fi[ID],fi[Due Days]),fi[Due Days])
2)m2=calculate(average(fi[Due Days]),distinct(fi[ID]))

Also note that i am using this measure in a card visual and if i have a slicer for ID, IF i filter by ID=110 the output should be 15(10+5)

1 ACCEPTED SOLUTION
DimaMD
Solution Sage
Solution Sage

Hi @Anonymous what is your desired result?
Screenshot_15.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

6 REPLIES 6
DimaMD
Solution Sage
Solution Sage

Hi @Anonymous what is your desired result?
Screenshot_15.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

25 is desired result, Lets say you add this measure to a card visual, you have a slicer for ID you select an ID 110 it should display 15(10+5).

@Anonymous  yes it work
Screenshot_16.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

@DimaMD  Thank you , it works

MattAllington
Community Champion
Community Champion

Create a dimension table containing all the unique IDs.
Join it to this table shown

remove the ID shown and replace with the ID from the dim table. 
then write a measure

AVERAGEX(NewDimTable,calculate(sum(originalTable[due days])))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Anonymous
Not applicable

@MattAllington like i said i need this for a card visual so will this method work, is there a way we could achieve the same functionality with a measure?

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.