How to calculate running total and apply in a measure

[Sha]

I have 2 tables:  Parcel data (Site, Parcel, Amount) and another table Site (Site, CAP)

Parcel table:

Site   Parcel    Amount

Site 1 Parcel1 3,136,900

Site 1 Parcel2 6,343,400

Site 1 Parcel3 8,248,500

Site 1 Parcel4 6,827,694

Site table:

Site    CAP

Site1   45,000

I want to show Site, Parcel, Amount, CAP, Potential (calculated measure: Amount *.2%) and Amount Due (calculated measure: if Potential is less than CAP, use it.).  My problem is CAP shows on every row when I join them and there is only 1 CAP for a site.  I really need to take Cap - parcel 1 Amount and apply it as the CAP to second row calculation and continue for each row.

Site   Parcel      Amount     Potential    CAP       Amount Due    Amount Due should be

Site 1 Parcel1   3,136,900     6,274      45,000      6,274               6,274 (6,274 is less than 45000)

Site 1 Parcel2   6,343,400   12,687      45,000    12,687              12,687 (12,687 is less than 45000-6274)

Site 1 Parcel3   8,248,500   16,497      45,000    16,497              16,497 (16,479 is less than 45000-6274-12687)

Site 1 Parcel4   6,827,694   13,655      45,000    13,655                9,542 (13,655 is not less than 45000-6274-12687-16497)

[Ashish_Mathur]

Hi,

In the absense of a Date column, how would one know the order in which rows should appear in the final table.  Aren't you missing a Date column?

[Jihwan_Kim]

Hi, @Sha 

Please check the below picture and the sample pbix file's link down below.

all measures are in the sample pbix file.

https://www.dropbox.com/s/relgjbyylvwk5hc/shasha.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Sha]

I put index in Site table and then did sort on Parcel table and changed code to below and it works.  Question now is this going to be huge performance drain?  I have 5 million rows in Parcel table.

Cap measure =

VAR siteid = SELECTEDVALUE(Site[Index])

VAR currentid =

CALCULATE(MAX ( Parcel[Index] ), Site[Index] = siteid)

VAR previousid =

CALCULATE (

CALCULATE(MAX ( Parcel[Index] ), Site[Index] = siteid),

FILTER ( ALL ( Parcel ), Parcel[Index] < currentid )

)

VAR previouspotentialcumulate =

CALCULATE (

[Potential],

FILTER ( ALL ( Parcel ), Parcel[Index] <= previousid ),

FILTER( all (Site), Site[Index] = siteid)

)

RETURN

IF( ISFILTERED(Parcel[Parcel]),

CALCULATE(SUM ( Site[CAP] ),Site[Index] = siteid) - previouspotentialcumulate

)

[Jihwan_Kim]

Hi, @Sha 

Thank you for your feedback.

Please try the measure to your actual data model. Is it slow? How is it slow? Is it direct query mode? Without knowing more details about your actual data model, it is hard to tell.

[Sha · Accepted solution]

Thanks for all your help, that got me going in the right direction and then was able to figure out different way to solve by determining an allocation percent to apply:

AllocPotential =

VAR siteid = SELECTEDVALUE(Site[Site])

VAR TotalPotential =

CALCULATE (

[Potential],

FILTER( all (Site), Site[Site] = siteid),

FILTER ( ALL ( Parcel ), Parcel[Site] = siteid)

)

return divide([Potential],TotalPotential)

and then using that with the CAP to determine New Potential

New Potential =

max(Site[CAP])*[AllocPotential]

[Sha]

Thank you, that seems to work for one site but when I added a second site mixed in with the first site data, it didn't work.  I also have other data in my tables like SiteID, ParcelID, TaxYear if that would help.  With your method, it's like I need to sort the data by Site and Parcel or group by Site and Parcel and repeat index for each.  Just can't figure it out.  Thanks in advance.