Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sadiahsapiee
Helper I
Helper I

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 :

image1.jpg

 

 

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

sadiahsapiee_1-1663579863449.png

 

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

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

9 REPLIES 9
sadiahsapiee
Helper I
Helper I

Comp3 Cost 18

Comp5 Cost 30

Comp1 cost 2.5

sadiahsapiee
Helper I
Helper I

 

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. 

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

sadiahsapiee_2-1663682594412.png

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. 

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] )
sadiahsapiee
Helper I
Helper I

Hi @tamerj1 

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

@sadiahsapiee 

What results didi you get using this proposed formula? 

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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors