Forum Discussion

IamTDR's avatar
IamTDR
Responsive Resident
5 years ago
Solved

Need Help Summing a Total

Hi I am trying to the PIU column to sum to 450 instead of 204.  Is this possible?  I have 23 distinct ProductIDs and two distint ProgCodes that will always be 200/250.  Not sure how to proceed...
Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    IamTDR This is a bit different than the Test_Data 🙂 Try this measure:

    PIU 2 =
    VAR _t =
        SUMMARIZE (
            Test_Data,
            Test_Data[AccountNumber],
            Test_Data[ProgramCode],
            "PIU", MIN ( [ProgramAdmissions] )
        )
    VAR _d =
        SUMX ( _t, VALUE ( [PIU] ) )
    RETURN
        _d

     

DataZoe's avatar
DataZoe
Microsoft Employee
5 years ago

IamTDR You could try this measure:

 

PIU =
VAR _min =
    MIN ( Test_Data[PIU] )
VAR _max =
    MAX ( Test_Data[PIU] )
VAR _same = _min = _max
RETURN
    IF ( _same, _min, _min + _max )

IamTDR's avatar
IamTDR
Responsive Resident
5 years ago
PIU =
VAR _min =
MIN ( Test_Data[ProgramAdmissions] )
VAR _max =
MAX ( Test_Data[ProgramAdmissions] )
VAR _same = _min = _max
RETURN
IF ( _same, _min, _min + _max )

This worked on my small sample data, but when I tried to use in my BI report I receive a "The end of the input was reached" message.
  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    5 years ago

    IamTDR hmm, not really sure why that error would show as it's usually syntax error. Maybe there are blanks in the bigger set?

    PIU =
    VAR _min =
        MIN ( Test_Data[ProgramAdmissions] )
    VAR _max =
        MAX ( Test_Data[ProgramAdmissions] )
    VAR _same =
        IF ( ISBLANK ( _min ), FALSE (), _min = _max )
    RETURN
        IF ( _same, _min, _min + _max )
    • IamTDR's avatar
      IamTDR
      Responsive Resident
      5 years ago

      Sorry, I still had the same error.  My dataset is large with many accounts and program codes.  Maybe thats my issue?

  • IamTDR's avatar
    IamTDR
    Responsive Resident
    5 years ago

    First thanks for the feedback.
    I am attempting to sum my PIU number but this number should be summed by AcctNumber and ProgramCode.
    In the example picture, the sum should be 711.
    Should I create a concatenate of accountnumber and programcode and use that field to sum my PIU?

    • DataZoe's avatar
      DataZoe
      Microsoft Employee
      5 years ago

      IamTDR This is a bit different than the Test_Data 🙂 Try this measure:

      PIU 2 =
      VAR _t =
          SUMMARIZE (
              Test_Data,
              Test_Data[AccountNumber],
              Test_Data[ProgramCode],
              "PIU", MIN ( [ProgramAdmissions] )
          )
      VAR _d =
          SUMX ( _t, VALUE ( [PIU] ) )
      RETURN
          _d

       

      • IamTDR's avatar
        IamTDR
        Responsive Resident
        5 years ago

        Thank you!  The lastest measure, PIU2 worked!

         

        Thanks again!