Mesure Actual Available balance

[MTrullàs]

Hello!

I have a table in Power BI, with the four columns like a screenshoot (ID; MARKET; PREICE; Available balance). I need to creat a mesure in DAX to get Actual Available balance like a this formul in EXCEL.

Thank you very much!

Regards

ID	MARKET	PREICE	Available balance	Actual Available balance
1	4I050	10972,9	12131354,41	12120381,51
2	4I050	11126,55	12131354,41	12109254,96
3	4I050	11126,55	12131354,41	12098128,41
4	4I050	11126,55	12131354,41	12087001,86
5	4I050	11126,55	12131354,41	12075875,31
6	4I014	11376,17	4822998,99	4811622,82
7	4I014	11376,17	4822998,99	4800246,65
8	4I014	11376,17	4822998,99	4788870,48
9	4I014	11376,17	4822998,99	4777494,31
10	4I014	11376,17	4822998,99	4766118,14
11	4I014	11376,17	4822998,99	4754741,97
12	4I014	11376,17	4822998,99	4743365,8
13	4I014	11376,17	4822998,99	4731989,63
14	4I014	11376,17	4822998,99	4720613,46
15	4I014	11376,17	4822998,99	4709237,29
16	4I014	11376,17	4822998,99	4697861,12
17	4I014	11376,17	4822998,99	4686484,95
18	4I014	11376,17	4822998,99	4675108,78
19	4I014	11376,17	4822998,99	4663732,61
20	4I014	11376,17	4822998,99	4652356,44
21	4I014	11376,17	4822998,99	4640980,27
22	4I014	11376,17	4822998,99	4629604,1
23	4I014	11376,17	4822998,99	4618227,93
24	4I014	11376,17	4822998,99	4606851,76
25	4I014	11376,17	4822998,99	4595475,59
26	4I014	11376,17	4822998,99	4584099,42
27	4I014	11376,17	4822998,99	4572723,25
28	4I014	11376,17	4822998,99	4561347,08
29	4I014	11376,17	4822998,99	4549970,91
30	4I014	11376,17	4822998,99	4538594,74
31	4I014	11376,17	4822998,99	4527218,57
32	4I014	11376,17	4822998,99	4515842,4
33	4I014	11376,17	4822998,99	4504466,23
34	4I014	11376,17	4822998,99	4493090,06
35	4I014	11376,17	4822998,99	4481713,89
36	4I014	11376,17	4822998,99	4470337,72
37	4I014	11376,17	4822998,99	4458961,55
38	4I014	11376,17	4822998,99	4447585,38
39	4I050	11385,38	12131354,41	12119969,03
40	4I014	11560,72	4822998,99	4811438,27
41	4I014	11560,72	4822998,99	4799877,55
42	4I014	11560,72	4822998,99	4788316,83

[lbendlin · Accepted solution]

Your implementation looks correct.  Please elaborate on "hasn't worked correctly". What is your expected outcome?

The Power Query implementation would be very similar.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZtdcuUoDEa3MpVnyiUJoZ8lzBq6ejOznlnVrGTwvUnAHRv7e0lVdx9hwUGAcfrXrw/+KB9Wrf9kKcm8Zfz13z//9j8LlfQsEbRlvv7ud/n1Id+8SBGxTWXJV5DXA9//jXjJN7B9A3kH+QD5BHkmNIDRAFQxo45Z0QDUMqOaGfXMqGhGTctuWv/efzIX7gGtfQb0UmXhIk035RHAI2BvUbZc84I+oIIP0MG3YuRbyDqgzRlJtE1iHWBoFxwNiLkPJLzJTUCOAC0putF6lCodnqCyyU3A5NkKtd4HXwcM0aKltdzyJmCIFiuivum601XRJzT0CTYHkLTbJzgaEGjAMC3ZZ6vdTQ0dpqUV76Pk63pQBodVUdM6me71QLnZTUqKBkymqWjSbUqGPsHRTr/W7kZvIKlt/P2EDvdGyHTjKSDnAKG+8K0DXuh4Qvg0Nc4DeA5oPV+5eYKgT6hogKIBDR0lQwMcHaVA+4CaNkIDGA0QNOBg2rJt6z6bog9o4KiagRk5mlGgAahnf6/duwuO4hmbffJaoh/osv/N/NrAGC4YXgeefQGTcbA65xXkG5aOgc37zBPLWLDP+Ri8FuEc8+2cz2++L/6N2zhcnPKBuQ3MbQiWfQy5/ZTA3k+c+s3vMzNCvrbEF68g30DeQH7Ybf0toZ9nXZb8sNv4PT5LPCE8h9v9tYtjbBnSpx4VIhowI/Dw2s+L7m3za/ZQsEKxsV3DisANgQ2BHYEDgROAmQiiGaIFohGLTIhGJsQjEyKSCTHJhKjsx/NRBVSYeaz9JzQfXKbZprmgIZcMuWTIJUMuGXLJkEuGXDLkkqG6FKguBXIpkEuZdsz7OSgK0Q2iDaIhlxJQ25DLSkjbFXJZIZd1cnm3q3KFVFaoLCtUlhVS+bpqaq/LGvGiZBt931gUMSku9HX1+Q5IMODzqmkfzP5+o5kjHyU5ZvN5y/RiWzGT8Q3oJytzu/tRX67brYPVwmlb8iWrANvmfFVk4+t8DRgHB8YhADafs+3gzfpZ1tsly89dNHk+vq3O+br3HPySVYBtQL4GsD73TXpxXKcQQLP5fMiM5mEQadM70g8WKDc7aNvflPlyOlh93jdTgAW02UGbJ6/GASg3O5TbeppZPs/XCWCBcnNgmXRgmfTDMrl/XPo+P/9k2/PxdXs+vn4ot2C9LjcHys0BbXEoN+pd48scgp8PbwDaAtAWB23LFSraAZXc4pq1xyYCKLYArMVhkWypW14uUHmw1oQXOeRhkVxPyASsJWAtJ2teKOdrzR8ssEgmsLelA2zMfVtvFvm82oTo8ZgJHapN+zzzqw1AaPJ2s2kKDW/7x9n9XHQ1d4SGN+n5Wj/zXS2SQsPb63IzNruaZ0I2tysxfaj/yTrQbgD5vry9Fon9m/P+jeITFfcio/5e9Pu65U337Zhpus88w3ng+4T3IfoUl7n1fU2xZet14FysyXiHOcV1bl18mqGneDvg2cat8yluWDKO4YHhecAzNl11VQ5WVdv4oHCK89y67r9SFytcDnib36nP8Iq1frB6O2ekYV2drPY3w4j1fJfJKpXoyaxxzKokNDJ1store3+rXSVT+QOZ75+XMPHGc/9G8lXaLFFMeSOZ8IrhiuHtG98X/jZ19RS3I66bLFt3DA8MTwj/vId5jPPA918p8q9P0Be4YK1XDNcZ15jOYKf4waqpTr8deEIbRGNONbDMMacNc9oYwyenD6ZAOzjd17BYdbUpMuytYakbNOzNMRwr1JbQOBphOCbVsEK1ilgyrE4NqlPDFl/DCtWwQjWsUB0rVGdkYFyg1B1S6opljtWpQ2uvY0odK1PHlAYhqQdDjuKPKr05lwS2ncbk9MEKE1CZBlamgTkNzGlgThNympjTxFbexJwmVqeJ1WliUnOS+mCCZUDDDjmthDitBO2mlQRqHFJaCTkhVYKMVoJOSJUgo5WgMq2USE+ZoNQZU8pQmVZGdtPKkFLGlDJUpJWhlbfypPTBDGCsSgU6IFXBnArmVLA6FWjprdKggRTMqmBWBVl6q2BSKya1IqfeWqFTb53ukZ6MeoWOSPXPe6S7nmJOsXukWiGn2DVSxa6RqmKFil0jVewaqWLXSPV4jXSPY1IVunOo80WSF3ddvhHU+SLpAT5fJD3BGcMFw6cr3/13yuzrf8xd4IrhDUvGMNznZHT/2rbEMavTTdKTrk43SU+SmW6SHrUuGF6hrs5XSU/wYXX/miO8sa5wzKo51lXMqmFWHatVx2rVn9fq7/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Numero Bastidor" = _t, Importador = _t, PRECIO_FINAL = _t, #"IMPORTE DISPONIBLE" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," €","",Replacer.ReplaceText,{"PRECIO_FINAL", "IMPORTE DISPONIBLE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Numero Bastidor", Int64.Type}, {"Importador", type text}, {"PRECIO_FINAL", Currency.Type}, {"IMPORTE DISPONIBLE", Currency.Type}},"en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Actual", (k)=> k[IMPORTE DISPONIBLE]-List.Sum(Table.SelectRows(#"Changed Type",each [Importador]=k[Importador] and [Numero Bastidor]<=k[Numero Bastidor])[PRECIO_FINAL]), Currency.Type)
in
    #"Added Custom"

[MTrullàs]

Thank you very much for your help.

I apologize for the delay in my response and acceptance of the solution. I have been having issues with currency formatting, which has caused some incidents.

However, I now have a problem determining how many 'Actual' values are positive and how many are negative for each importer. I have created the following measure (Measure = IF([Actual] < 0, "no", "yes")), but when I apply it in a matrix, it does not work for me. How could I solve this? Thank you very much in advance. Regards

[MTrullàs]

To try to provide more detail on this issue. Why does the following measure also count negative "Actual" values? 

Conteo_Actual_Positivos =

COUNTROWS(

    FILTER(

        'SCUD',

        'SCUD'[Actual] > 0

    )

)

How could I solved it?

[lbendlin · Accepted solution]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

[lbendlin]

You have an error in line 39

Actual = 
var I = max('Table'[ID])
return max('Table'[Available balance])-CALCULATE(sum('Table'[PRiCE]),'Table'[ID]<=I)

[MTrullàs]

Thank you very much for the response.

I've tried the proposed solution, but it hasn't worked correctly for me, and I don't understand why. I'm attaching a link where you can find the example PBI. It would also be extremely helpful if I could apply this same requested logic in Power Query. Thank you very much in advance.

https://drive.google.com/file/d/1I2wEI0RTynwez760qE4wCvESsZb2hhwq/view?usp=drive_link

[lbendlin · Accepted solution]

Your implementation looks correct.  Please elaborate on "hasn't worked correctly". What is your expected outcome?

The Power Query implementation would be very similar.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZtdcuUoDEa3MpVnyiUJoZ8lzBq6ejOznlnVrGTwvUnAHRv7e0lVdx9hwUGAcfrXrw/+KB9Wrf9kKcm8Zfz13z//9j8LlfQsEbRlvv7ud/n1Id+8SBGxTWXJV5DXA9//jXjJN7B9A3kH+QD5BHkmNIDRAFQxo45Z0QDUMqOaGfXMqGhGTctuWv/efzIX7gGtfQb0UmXhIk035RHAI2BvUbZc84I+oIIP0MG3YuRbyDqgzRlJtE1iHWBoFxwNiLkPJLzJTUCOAC0putF6lCodnqCyyU3A5NkKtd4HXwcM0aKltdzyJmCIFiuivum601XRJzT0CTYHkLTbJzgaEGjAMC3ZZ6vdTQ0dpqUV76Pk63pQBodVUdM6me71QLnZTUqKBkymqWjSbUqGPsHRTr/W7kZvIKlt/P2EDvdGyHTjKSDnAKG+8K0DXuh4Qvg0Nc4DeA5oPV+5eYKgT6hogKIBDR0lQwMcHaVA+4CaNkIDGA0QNOBg2rJt6z6bog9o4KiagRk5mlGgAahnf6/duwuO4hmbffJaoh/osv/N/NrAGC4YXgeefQGTcbA65xXkG5aOgc37zBPLWLDP+Ri8FuEc8+2cz2++L/6N2zhcnPKBuQ3MbQiWfQy5/ZTA3k+c+s3vMzNCvrbEF68g30DeQH7Ybf0toZ9nXZb8sNv4PT5LPCE8h9v9tYtjbBnSpx4VIhowI/Dw2s+L7m3za/ZQsEKxsV3DisANgQ2BHYEDgROAmQiiGaIFohGLTIhGJsQjEyKSCTHJhKjsx/NRBVSYeaz9JzQfXKbZprmgIZcMuWTIJUMuGXLJkEuGXDLkkqG6FKguBXIpkEuZdsz7OSgK0Q2iDaIhlxJQ25DLSkjbFXJZIZd1cnm3q3KFVFaoLCtUlhVS+bpqaq/LGvGiZBt931gUMSku9HX1+Q5IMODzqmkfzP5+o5kjHyU5ZvN5y/RiWzGT8Q3oJytzu/tRX67brYPVwmlb8iWrANvmfFVk4+t8DRgHB8YhADafs+3gzfpZ1tsly89dNHk+vq3O+br3HPySVYBtQL4GsD73TXpxXKcQQLP5fMiM5mEQadM70g8WKDc7aNvflPlyOlh93jdTgAW02UGbJ6/GASg3O5TbeppZPs/XCWCBcnNgmXRgmfTDMrl/XPo+P/9k2/PxdXs+vn4ot2C9LjcHys0BbXEoN+pd48scgp8PbwDaAtAWB23LFSraAZXc4pq1xyYCKLYArMVhkWypW14uUHmw1oQXOeRhkVxPyASsJWAtJ2teKOdrzR8ssEgmsLelA2zMfVtvFvm82oTo8ZgJHapN+zzzqw1AaPJ2s2kKDW/7x9n9XHQ1d4SGN+n5Wj/zXS2SQsPb63IzNruaZ0I2tysxfaj/yTrQbgD5vry9Fon9m/P+jeITFfcio/5e9Pu65U337Zhpus88w3ng+4T3IfoUl7n1fU2xZet14FysyXiHOcV1bl18mqGneDvg2cat8yluWDKO4YHhecAzNl11VQ5WVdv4oHCK89y67r9SFytcDnib36nP8Iq1frB6O2ekYV2drPY3w4j1fJfJKpXoyaxxzKokNDJ1store3+rXSVT+QOZ75+XMPHGc/9G8lXaLFFMeSOZ8IrhiuHtG98X/jZ19RS3I66bLFt3DA8MTwj/vId5jPPA918p8q9P0Be4YK1XDNcZ15jOYKf4waqpTr8deEIbRGNONbDMMacNc9oYwyenD6ZAOzjd17BYdbUpMuytYakbNOzNMRwr1JbQOBphOCbVsEK1ilgyrE4NqlPDFl/DCtWwQjWsUB0rVGdkYFyg1B1S6opljtWpQ2uvY0odK1PHlAYhqQdDjuKPKr05lwS2ncbk9MEKE1CZBlamgTkNzGlgThNympjTxFbexJwmVqeJ1WliUnOS+mCCZUDDDjmthDitBO2mlQRqHFJaCTkhVYKMVoJOSJUgo5WgMq2USE+ZoNQZU8pQmVZGdtPKkFLGlDJUpJWhlbfypPTBDGCsSgU6IFXBnArmVLA6FWjprdKggRTMqmBWBVl6q2BSKya1IqfeWqFTb53ukZ6MeoWOSPXPe6S7nmJOsXukWiGn2DVSxa6RqmKFil0jVewaqWLXSPV4jXSPY1IVunOo80WSF3ddvhHU+SLpAT5fJD3BGcMFw6cr3/13yuzrf8xd4IrhDUvGMNznZHT/2rbEMavTTdKTrk43SU+SmW6SHrUuGF6hrs5XSU/wYXX/miO8sa5wzKo51lXMqmFWHatVx2rVn9fq7/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Numero Bastidor" = _t, Importador = _t, PRECIO_FINAL = _t, #"IMPORTE DISPONIBLE" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," €","",Replacer.ReplaceText,{"PRECIO_FINAL", "IMPORTE DISPONIBLE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Numero Bastidor", Int64.Type}, {"Importador", type text}, {"PRECIO_FINAL", Currency.Type}, {"IMPORTE DISPONIBLE", Currency.Type}},"en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Actual", (k)=> k[IMPORTE DISPONIBLE]-List.Sum(Table.SelectRows(#"Changed Type",each [Importador]=k[Importador] and [Numero Bastidor]<=k[Numero Bastidor])[PRECIO_FINAL]), Currency.Type)
in
    #"Added Custom"