Parameters to calculate custom values for some categories

[FrancescoSCP]

Hi all,

i have a table like this:

activity   hour   value

A            2         10

B            3          15

C            4           20

By default value is calculated hour*5.

My goal is to set some parameters (or custom table) to multiply hour with a different cost for each activity.

For example hourly cost for activity A to 20, so value has to be 40.

For activity B 10 per hour, so value has to be 30. And for activity C default value.

The goal is to edit those hourly cost with a parameter (or edit values in a table).

Any suggestions?

Regards,

Francesco

[v-eachen-msft]

Hi @FrancescoSCP ,

You could use SWITCH() function to get a new column or measure:

Column =
SWITCH (
    test[activity],
    "A", test[hour] * 20,
    "B", test[hour] * 10,
    test[hour] * 5
)

Measure =
VAR a =
    SELECTEDVALUE ( test[activity] )
VAR b =
    SELECTEDVALUE ( test[hour] )
RETURN
    SWITCH ( SELECTEDVALUE ( test[activity] ), "A", b * 20, "B", b * 10, b * 5 )

Here is the result.

[amitchandak]

Create a table with an hourly cost of the activity.

Join this with this table.

Using lookup value, populate the value in a column for the current table 

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

.Create another column to multiply the two

[FrancescoSCP]

Hi,

yes it works but the end user cannot edit the hourly cost.

The best would be a write back option: with power apps i can only connect to cloud SQL server with my free account. To use data gateway it requires a plan.

Any suggestion?

Regards,

Francesco