Forum Discussion

sadiahsapiee's avatar
sadiahsapiee
Helper I
3 years ago
Solved

Dynamic value calculation based on input

Hi, I have below scenario, I have been tried more than a month to get the solution but still not successful :     in above table the total = 351 and I put in example 35004  User select Sal...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi sadiahsapiee 
    Please try

    Total Cost of Critical =
VAR T1 =
    ADDCOLUMNS (
        VALUES ( 'Table'[Component] ),
        "@Cost", CALCULATE ( SELECTEDVALUE ( 'Table'[Cost] ) ),
        "@Category", [Category]
    )
VAR T2 =
    FILTER ( T1, [@Category] = "Critical" )
RETURN
    SUMX ( T2, [@Cost] )
sadiahsapiee's avatar
sadiahsapiee
Helper I
3 years ago

After we get total Cost of Critical Category from previous formula, i want to calculate cost cumulation using delivery time low to high 

What i did before( without considering category)

1.
Cost_Cumulation =
    CALCULATE(SUM'Table1'[Cost], ALL('Table1'[Component]),
        FILTER(ALL('Table'[Delivery Time),AND('Table'[Delivery Time >=MIN('Table'[Delivery Time]), NOT(ISBLANK('Table'[Delivery Time])))))
2.
% Cost Cumulation = [Cost Cumulation]/ CALCULATE([Cost Cumulation], 'Table1'[Delivery Time]=0)

Now the calculation need to consider Critical category only. 
thank in advance for your support. 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi sadiahsapiee 

please try

Cumulative Cost of Critical =
VAR CurrentDeliveryTime =
    SELECTEDVALUE ( 'Table'[Delivery Time] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Component] ),
        "@Cost", CALCULATE ( SELECTEDVALUE ( 'Table'[Cost] ) ),
        "@Category", [Category],
        "@DeliveryTime", CALCULATE ( SELECTEDVALUE ( 'Table'[Delivery Time] ) )
    )
VAR T2 =
    FILTER (
        T1,
        [@Category] = "Critical"
            && [@DeliveryTime] >= CurrentDeliveryTime
    )
RETURN
    SUMX ( T2, [@Cost] )