my data not summing correctly

[jonnyA]

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 the "Co Sign Amount" ...

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]))

But for some reason it is not summing one of the $25 as seen in the pic below.  So it has to be for the 99497 or the 99498.

Does anyone know how I can get this to sum correctly.  Currently showing $60, but it should be $95.

[PaulDBrown]

@jonnyA 

Perhaps

Provider Payout =
VAR IfVar = IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value])

RETURN

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

IfVar), [Billed CPT])

Again, you might need to split this into  two measures.

[jonnyA]

@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]

@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]

@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]

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

[jonnyA]

@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])

-----

[PaulDBrown · Accepted solution]

@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]

@PaulDBrown Oh my goodness, that worked!!!   Thank you so much!!!!

[PaulDBrown]

Can you provide a screenshot of the model to see the relationships between the tables?

[jonnyA]

@PaulDBrown , Sorry, I am new to Power BI.  I never knew this existed, but here you go!

[PaulDBrown]

@jonnyA 

Try:

Provider Payout =
VAR _IF = IF([CPT Co Sign Value]>=0, [Visits] * [CPT Co Sign Value]))

RETURN

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

_IF), [_IF])

If that doesn't work, you might need to split it into 2 measures:

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

2) Provider Payout = 

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

[If measure], [If measure])

[jonnyA]

@PaulDBrown 

I have a couple red squigglies ... any recommendations on how to fix?