Forum Discussion

jonnyA's avatar
jonnyA
Responsive Resident
5 years ago
Solved

my data not summing correctly

Hello, I have a matrix where only one of my "Co Sign Amount's" is not summing in the total.  Do you know how I can make it sum?  It is currently off $25.   Here is the formula that I am using for ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    jonnyA 

     

    Ok, try this then:

    If Measure = IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value])

     

    Provider Payout 5 = SUMX(Sheet1, [If measure]).

    If that doesn't work, then we will need to create the SUMMARIZE expression but including all the columns in the visual.

     

jonnyA's avatar
jonnyA
Responsive Resident
5 years ago

PaulDBrown ,

 

Thank you for your help on this issue I am having!  I really appreciate the assisitance!!!

 

Ok, i think that worked a little bit, however, i am still off (I am off a a lot overall).  The total I had questioned in my first request went from $65 to $80, but it should be $90.

 

Also, the total went from $20,520 (Provider Payout) to $175,800 with the new measure (Provider Payout 3)

 

I am not sure what you mean on splitting into 2 measures?

Thank you in advance!

 

 

 

 

 

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

jonnyA 

Spltting into 2 measures would be:

1) If Measure =  IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value])

2) Final measure:

Provider Payout = 

SUMX(SUMMARIZE(Sheet1, Sheet1[Co-Sign Bonus], "Billed CPT",

[If measure]), [Billed CPT])

 

PS. Actually, seeing your original screenshot, which table is the period field and the other fields in the matrix from? You might need to include them in the SUMMARIZE expression.

 

 

  • jonnyA's avatar
    jonnyA
    Responsive Resident
    5 years ago

    PaulDBrown ,

    Ok, thank you for the assistance!

     

    I split the measure into 2 (Provider Payout 4), however, this time I get the same #'s as I initially had.  We're back to $65.  Should be $90.  I do not understand why Power BI is not recognizing one of those $25 Provider Payouts?

     

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      Can you provide a screenshot of the model (relevant tables) & one of the visual to see the rows/ columns you are using? 

      • jonnyA's avatar
        jonnyA
        Responsive Resident
        5 years ago

        PaulDBrown

        Hey!  Let me know if this helps!

        I gave you the measure I am using with the formula.  Hopefully the screen shots help.  Which also include the filters I am using!  You can see all the measure that I amusing that are checked marked.  Thank you again for all your help!

         

        Co-Sign Bonus Amount =

        [Visits] * MAXA(Sheet1[Co-Sign Bonus])
        -----
        If Measure = IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value])
        -----
        Provider Payout =
        VAR ProviderPayout =
        SUMMARIZE(Sheet1, Sheet1[Co-Sign Bonus], "Billed CPT",
        [Visits] * [CPT Co Sign Value] )

        RETURN
        SUMX(ProviderPayout,
        IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value]))
        -----
        Provider Payout 4 =

        SUMX(SUMMARIZE(Sheet1, Sheet1[Co-Sign Bonus], "Billed CPT",

        [If measure]), [Billed CPT])
        -----
        CPT Per Visit 3 = SUMX(

        ADDCOLUMNS(

        DISTINCT( vw_Table_Payments[CPT_Code]),

        "CPTPerVisit", CALCULATE(DISTINCTCOUNT(vw_Table_Payments[voucher_number]))

        ),

        [CPTPerVisit])
        -----