Forum Discussion
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 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.
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.
12 Replies
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])
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.
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!
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.
