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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors