cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Amateur Needing Help with Calculated Measure

Can someone kindly help  me with this calculated measure?

I am trying to get this measure not to exclude Line 2 Charge Amount Totals.  The sum for Charge Line ID 8264 should be \$576.  My measure doesn't take into account some Charges may be on Line 2, 3, etc.

Here is my current measure. Thank you in advance!!!!!

IsLatest =
VAR charge_id = Charges[Charge ID]
VAR latest_charge_id_date =
CALCULATE(
ALL(Charges),
Charges[Charge ID] = charge_id
)

RETURN
IF(
TRUE(),
FALSE()
)
 Total Charge MRN Charge ID Charge Line ID Acct Date CPT Last Modified Date Line Number Is Latest 576 3930 8063 8264 4/1/22 Q9967 4/1/22 2 False 576 3930 8063 8264 4/1/22 Q9967 4/5/22 2 False 576 3930 8063 8264 4/1/22 Q9967 4/20/22 2 False 956.72 3930 8064 8265 4/1/22 76700 4/1/22 1 False 956.72 3930 8064 8265 4/1/22 76700 4/5/22 1 False 956.72 3930 8064 8265 4/1/22 76700 4/20/22 1 True

1 ACCEPTED SOLUTION
Super User

@TheSweeper , Try a measure like

Measure =
VAR __id = MAX ('Table'[Charge Line ID] )
VAR __date = CALCULATE ( MAX('Table'[Charge Last Modified Date] ), ALLSELECTED ('Table' ), 'Table'[Charge Line ID] = __id )
return
CALCULATE ( Sum ('Table'[Total Charge] ), VALUES ('Table'[Charge Line ID] ),'Table'[Charge Line ID] = __id,'Table'[Charge Last Modified Date] = __date )

7 REPLIES 7
Helper I

Woohooo I figured it out

@amitchandak Thank you so much for your help!

I  used this formula to return the correct total.

SUMX(SUMMARIZE(Charges, Charges[Charge Line ID], "Last Mod Charge", [Measure]), [Last Mod Charge])
Helper I

If you have time do you mind taking a stab at this? Unfortunately I can't continue building my report without this formula being completely correct in its output.  😞

Helper I

@DataInsights - I figured it out 🙂

New Member

Also an amateur, but does using filter within the CALC help you here?

https://community.powerbi.com/t5/Desktop/DAX-Calculate-the-Max-for-a-Group/td-p/64645

Helper I

I tried but it didn't work. Thank you though!

Super User

@TheSweeper , Try a measure like

Measure =
VAR __id = MAX ('Table'[Charge Line ID] )
VAR __date = CALCULATE ( MAX('Table'[Charge Last Modified Date] ), ALLSELECTED ('Table' ), 'Table'[Charge Line ID] = __id )
return
CALCULATE ( Sum ('Table'[Total Charge] ), VALUES ('Table'[Charge Line ID] ),'Table'[Charge Line ID] = __id,'Table'[Charge Last Modified Date] = __date )

Helper I

Your measure now gives me the correct data just the incorrect total. Truly appreciate your help!!!

Any suggestions on how I can show the correct total? 576+956.72+1,6803.96 = \$3,213.68

I added the new measure below the screenshot.

Measure =
VAR __id = MAX ('Charges'[Charge Line ID])
VAR __date = CALCULATE ( MAX('Charges'[Charge Last Modified Date] ), ALLSELECTED ('Charges' ), 'Charges'[Charge Line ID] = __id )
return
CALCULATE ( Sum ('Charges'[Total Charge] ), VALUES ('Charges'[Charge Line ID] ),'Charges'[Charge Line ID] = __id,'Charges'[Charge Last Modified Date] = __date )

Sincerely,
Amanda Cross

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.