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 Sale Item & enter Quantity in respective month input box (direct manual input in PBI report) . 

The sale item has a set of component (~ 100 component). 

 

Yellow columns are dynamic value based on Input demand (sum of user input quantity).

How I am going to calculate total cost of critical component (this will change based on user enter value and sale item they selected)? 

 

  • 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] )

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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

    Hi tamerj1 

    But Percentage & Category need to be calculated first because it based on quantity input total that user entered. 

      • sadiahsapiee's avatar
        sadiahsapiee
        Helper I
        3 years ago

        Hi tamerj1 

         

        Ok, I know what you means.. I have already have category measure and just use that in formula. 
        I have tried your formula and it works... thank you soo much..  😄

  • sadiahsapiee's avatar
    sadiahsapiee
    Helper I
    3 years ago

     

    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] )

    Hi tamerj1 

    Is it possible to calculate cost cumulation for Delivery Time lowest to higest? 
    I tried this formula but seem not work, it give the the Cost value of the component.
    CALCULATE (SUMX(T2, [@Cost]). FILTER(ALL('Table'[Delivery Time]). 'Table'[Delibery Time]>=MIN('Table[Delivery Time])))

    I am want result like below:

     CostDelivery TimeCost Cummulation
    Comp318550.5
    Comp530532.5
    Comp12.5122.5

     

    Really appreciate your help and thanks in advance. 

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      sadiahsapiee 
      Sorry I did not understand the question. Would you please clarify further perhaps more details and some screenshots.

      • 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.