Search code examples
powerbisumdaxdistinct-values

Summarizing numbers over Distinct values in DAX Power BI

I am having trouble receiving correct result when trying to sum numbers over Distinct values (in DAX Power BI)

I have the following table - Tbl_Eposode:

enter image description here

I expect to have total numbers of [Episode] = 12

But I keep having SUM of [Episode] = 36.

My code just summarizes all Episode values instead only summarizing unique Episodes (by EpisodeID, ProgramID))

This is my code:

     # Pre Homeless Days = CALCULATE(SUM('Tbl_Episode'[Episode]),                    
                                     ALLEXCEPT('Tbl_Episode','Tbl_Episode'[EpisodeID],
                                                             'Tbl_Episode'[ProgramID],
                                                             'Tbl_Episode'[ClientID]))

Please Help!

Solution

  • As David Brownse says, this really needs remodelling. However, if you are adamant that this is the way to go then:

    # Pre Homeless Days = 
  SUMX( SUMMARIZE(Tbl_Episode, Tbl_Episode[EpisodeID], Tbl_Episode[ProgramID], Tbl_Episode[ClientID],Tbl_Episode[Episode]), Tbl_Episode[Episode])