Creating measure to calculate sum from a column based on other column

[Anonymous]

Hello,

I am trying to calculate sum of achieved to plot against milestones of an asset in a matrix table. 

Complication here is for milestone 1 i have one milestone entry so it picks 10, for milestone two i want to pick 15 but i end up getting 30 while for milestone 3 i am looking to get 18. its a bit tricky and i am stuck. any help is appreciateed thanks.

asset	milestone	activity	achieved
A	milestone1	milestone 1 activity	10
A	milestone2	Milestone 2 activity a	15
A	milestone2	Milestone 2 activity b	15
A	milestone3	Milestone 3 activity a	2
A	milestone3	Milestone 3 activity b	5
A	milestone 3	Milestone 3 activity c	11

[amitchandak]

@Anonymous , Try a new measure like

measure =
sumx(summarize(Table,Table[asset],Table[milestone],Table[achieved]),[achieved])

[admin_muntap]

Sounds like the matrix is doing its work right based on data.

If you are looking for milestone2 to return 15 (not 30) and milestone3 to return 30 (all the values in 3 rows), what is your selectio criteria?

Which rows to keep and which ones to skip?

[parry2k]

@Anonymous provide the business logic why you want to sum up for milestone 3 and not for 2. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hello, thankyou for the reply. The bussiness logic is  that i have milestones like permissions which are done for entirety of  the asset while some like consruction which are done in parts. Need to calculate sum of achieved till target date. Overall achievement possible is 15 but due to multiple permission entry in column it goes to being 30 while in case of construction it needs to be summed to get 15. While there are also activity like engeneeerig which are one entry per asset location and works fine in a simple  measure.  

asset	milestone	activity	achieved	Target date
gas station	Engineering	Engineering complete	15	10/5/2020
gas station	Permission applied	Permission for construction	15	15/5/2020
gas station	Permission applied	Permission for Land purchase	15	15/5/2020
gas station	Permission received	Permission for land received	15	5/6/2020
gas station	Permission received	Permission for construction received	15	5/6/2020
gas station	Construct	Construction of x complete	11	30/5/2020
gas station	Construct	Construction of y complete	4	10/6/2020

Thankyou for your time

-A newbie Pbi developer

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can use IF or SWITCH function to judge the condition fot the calculation.

Try measure like this:

Measure =
SWITCH (
    TRUE (),
    MAX ( 'Table'[milestone] ) = "Construct", CALCULATE (
        SUM ( 'Table'[achieved] ),
        ALLEXCEPT ( 'Table', 'Table'[asset ], 'Table'[milestone] )
    ),
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[achieved], 1 ),
        ALLEXCEPT ( 'Table', 'Table'[milestone] )
    )
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.