cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Responsive Resident

## 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.

1 ACCEPTED SOLUTION
Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

12 REPLIES 12
Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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?

Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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?

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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(

DISTINCT( vw_Table_Payments[CPT_Code]),

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

),

[CPTPerVisit])
-----
Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Responsive Resident

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors