Forum Discussion

keckraguilar's avatar
keckraguilar
Frequent Visitor
1 year ago
Solved

Measure is creating duplicate rows in a table

I am trying to sum charges for the two previous months. The grand total is right but the grand total is duplicating down every row. Below are the two DAX formulas I am using. I think the issue is with the "ALL" being in the formula but I can't figure it out. I also included a screenshot of the fields in my table. 

 

 

Total Charges Current Month =
  VAR __MaxMonth = MAXX( ALL( 'Volume Files' ), 'Volume Files'[Month Number] )
  VAR __Result = CALCULATE(SUM('Volume Files'[Total Charges]), FILTER( ALL( 'Volume Files' ), [Month Number] = __MaxMonth), FILTER( ALL( 'Volume Files' ), 'Volume Files'[Discharge Date - Fiscal Year]="FY2024" ))
RETURN
  __Result

 

 

 

Total Charges Previous Month =
  VAR __MaxMonth = MAXX( All( 'Volume Files' ), 'Volume Files'[Month Number] )
  VAR __Result = CALCULATE(SUM('Volume Files'[Total Charges]), FILTER( ALL( 'Volume Files' ), [Month Number] = __MaxMonth - 1 ), FILTER( All( 'Volume Files' ), 'Volume Files'[Discharge Date - Fiscal Year]="FY2024" ))
RETURN
  __Result

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi keckraguilar ,

     

    You can change your expression to something like this.

    Total Charges Current Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

     

    Total Charges Previous Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

3 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    You can create one measure for your charges as Total Charges = SUM( 'Volume Files'[Total Charges] ), then create a second measure for prior month like Total Charges Previous Month = CALCULATE ([Total Charges], PREVIOUSMONTH ('DateTable', [Date]). Then, when you filter to the year and month your [Total Charges] measure will show that amount and [Total Charges Previous Month] will return the prior month. 

    PREVIOUSMONTH function (DAX) - DAX | Microsoft Learn

    • keckraguilar's avatar
      keckraguilar
      Frequent Visitor
      1 year ago

      I am trying to make the formula dynamic and not have to manually change the date field every month when data refreshes

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi keckraguilar ,

     

    You can change your expression to something like this.

    Total Charges Current Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

     

    Total Charges Previous Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.