Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average based on distinct count and summing up

Input TableOutput

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)

6 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

       

  • DimaMD's avatar
    DimaMD
    Solution Sage
    4 years ago

    Hi Anonymous what is your desired result?


    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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