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.
THanks in advance.

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

     

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    IamTDR , Not very clear try a new measure like

     

    sumx(values('test_date'[project_id]),[PIU])

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • 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

      Not sure how to do that, plus I have only real data that I cannot share online.