I have a orders table in PowerBI with in 1 column the order category and in 1 the revenue per order. One of the category's is contracts, for this category I would like to multiply the total revenue per order by 8, 10 or 12. I want to be able to decide this based on a slicer I have put in the page, how can I do this?

If possible I would prefer to stay in DirectQuery but can switch to import if required

I tried using a selectedvalue function and managed to get the correct multiplier in a measure but when I try to multiply it with the revenue it doesnt work for some reason.

Below is what I tried which instead of letting me change the multiplier it stayed at 10 exactly but when I made a smal Visual with the Selected_Multiplier it did show the multiplier correctly.

OrderPrice = 
VAR containsCContract = IF(
    NOT(ISBLANK(Orderinfo[ArtikelCategory])) && CONTAINSSTRING(Orderinfo[ArtikelCategory], "Contract"),
    TRUE(), 
    FALSE()
)

VAR Multiplier = VALUE([Selected_Multiplier])
   

RETURN 
IF(ContainsContract, Orderinformatie[OmzetperOrder] * Multiplier, Orderinformatie[OmzetperOrder])

Selected_Multiplier = SELECTEDVALUE(Multiplier[MultiplierOrders], 10)

Columns in order: Amount, Buy price, amount of hours spend, Sell price, Margin per order, Category, Margin with the supposed multiplier for contracts

Raw data with column names

Sample data

I have a orders table in PowerBI with in 1 column the order category and in 1 the revenue per order. One of the category's is contracts, for this category I would like to multiply the total revenue per order by 8, 10 or 12. I want to be able to decide this based on a slicer I have put in the page, how can I do this?

If possible I would prefer to stay in DirectQuery but can switch to import if required

I tried using a selectedvalue function and managed to get the correct multiplier in a measure but when I try to multiply it with the revenue it doesnt work for some reason.

Below is what I tried which instead of letting me change the multiplier it stayed at 10 exactly but when I made a smal Visual with the Selected_Multiplier it did show the multiplier correctly.

OrderPrice = 
VAR containsCContract = IF(
    NOT(ISBLANK(Orderinfo[ArtikelCategory])) && CONTAINSSTRING(Orderinfo[ArtikelCategory], "Contract"),
    TRUE(), 
    FALSE()
)

VAR Multiplier = VALUE([Selected_Multiplier])
   

RETURN 
IF(ContainsContract, Orderinformatie[OmzetperOrder] * Multiplier, Orderinformatie[OmzetperOrder])

Selected_Multiplier = SELECTEDVALUE(Multiplier[MultiplierOrders], 10)

Columns in order: Amount, Buy price, amount of hours spend, Sell price, Margin per order, Category, Margin with the supposed multiplier for contracts

Raw data with column names

Sample data

• powerbi
• dax
• powerbi-desktop

• could you pls provide some sample data and expected output? – Ryan Commented Mar 17 at 0:52
• Yeah sure, the multiplier table is just a simple 3 row column with the number 8 10 and 12 formatted as a number. The "OmzetperOrder column is a simple column that does the selling price x the amount and then compare it against the buy price x the amount to get the total profit. The expected output I hope to get is a total number for each order that is a contract that shows the total profit multiplied by the selected number in the slicer on the visual page. If you need any specific sample data let me know but since most of it is company crucial data I would make some random data. – Rikpro43 2 Commented Mar 17 at 9:09
• @Ryan I added a picture for the sample data like you requested – Rikpro43 2 Commented Mar 17 at 9:50
• which row * 8? what's more, I can't see the column name in your screenshot. could you pls also provide the expected output based on the screenshot you provided? – Ryan Commented Mar 17 at 10:06
• The row that I want to be able to do * 8, 10 or 12 is the 5th column, the result is the last column but as you can see now it just does it * 10 and whatever I choose in my slicer it keeps doing * 10 even when I can see the measure that it is multiplying with is 8. – Rikpro43 2 Commented Mar 17 at 13:53

1 Answer 1

pls see if this is what you want.

this is the sample data I used

| ArtikelCategorie | Order-id | value |

| ---------------- | -------- | -------- |

| Uren | 89557 | 50,00 |

| Uren | 89556 | 50,00 |

| Producten | 89555 | 406,64 |

| Uren | 89555 | 50,00 |

| Producten | 89554 | 19,50 |

| Uren | 89553 | 50,00 |

| Uren | 89552 | 117,50 |

| Uren | 89551 | 50,00 |

| Uren | 89550 | 50,00 |

| Uren | 89549 | 50,00 |

| Producten | 89548 | 1.974,13 |

| Uren | 89548 | 50,00 |

| Uren | 89547 | 50,00 |

| Uren | 89546 | 50,00 |

| Producten | 89545 | 302,80 |

| Uren | 89545 | 400,00 |

| Uren | 89544 | 50,00 |

| Uren | 89543 | 50,00 |

| Uren | 89542 | 50,00 |

| Contract | 89541 | 2,42 |

| Producten | 89541 | 1.561,03 |

| Contract | 89540 | 41,00 |

so the last value for contract is the ID 89540 and value is 41

you can create a table with 8, 10 and 12

Measure =
VAR _order =
    MINX (
        FILTER ( 'Table', 'Table'[ArtikelCategorie] = "Contract" ),
        'Table'[Order-id]
    )
RETURN
    MAXX ( FILTER ( 'Table', 'Table'[Order-id] = _order ), 'Table'[value] )
        * MAX ( 'Table (2)'[value] )