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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.