Multiplier depending on critera

[Kfitzek2022]

Hello,

I would like to create a measure or column based off critera that will dicate to multiply a set of data or not. In the table below, I would like to multiply anything that has the channel of "Disty" by 6%. Any help would be great! Thank you!

Channel	Cost
Direct	$  100.00
Disty	$  200.00
Disty	$  100.00
Direct	$  500.00
Disty	$  600.00

[d_rohlfs · Accepted solution]

Hey there @Kfitzek2022,

What you need to do is create a DAX Calculated column. The formula should look like this:

Column Name = If('Table'[Channel] = "Disty", 'Table'[Cost] * 1.06, 'Table'[Cost])

If this is correct please think about marking it as a solution.

[Syk]

A few things to consider when choosing a calculated column vs measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/#:~:text=The%20difference%20is....

Try the measure I posted earlier to see if you get your desired result.

[Kfitzek2022]

Hello @Syk I tried your solution as well, and though it works in isolated instances. It also like the column doesn't work when I use certain filters. Any ideas how I get it to apply to when I filter?

[Syk]

Depends on your relationships and the filter.. Can you give an example?

[Kfitzek2022]

So I added the table below to show sort of what I mean. I'm only using one data source right now, so no relationships have been made. I simply will choose a Company, and it will sum up all the ones labled "Disty" not just Company A for instance.

Company	Channel	Cost
A	Direct	$  100.00
B	Disty	$  200.00
C	Disty	$  100.00
A	Direct	$  500.00
A	Disty	$  600.00

[Syk]

You're trying to sum this up by company? A little confused by this response.
Right now this is what I'm showing by copying your example.

If you set Cost to sum, you'll get 

And if you want it rolled up by company, just remove the other details

[Kfitzek2022]

Hey @Syk That is correct. Trying to sum things up by each company. So if I choose Company A, I just want back the sum of the disty cost by company A. I want to be able to do that with each company I select. 

[Syk · Accepted solution]

Oh you're saying you just don't want the Direct cost? Only disty?

Disty Cost = CALCULATE (
        SUM ( 'table'[cost] ),
        'table'[Channel] = "Disty"
    ) * 1.06

[Kfitzek2022]

This is exactly what I needed! Thank you so much!

[d_rohlfs · Accepted solution]

Hey there @Kfitzek2022,

What you need to do is create a DAX Calculated column. The formula should look like this:

Column Name = If('Table'[Channel] = "Disty", 'Table'[Cost] * 1.06, 'Table'[Cost])

If this is correct please think about marking it as a solution.

[Kfitzek2022]

This works, but when I filter on other things in the report, it is multiplying all cost still instead of what is filtered. Anyway to get around this? For instance. If had the same table as above but added in one more column for filtering. Example below.

Company	Channel	Cost
A	Direct	$  100.00
B	Disty	$  200.00
C	Disty	$  100.00
A	Direct	$  500.00
A	Disty	$  600.00

[Syk]

___measure =
VAR disty =
    CALCULATE (
        SUM ( 'table'[cost] ),
        'table'[Channel] = "Disty"
    ) * 1.06
VAR other =
    CALCULATE (
        SUM ( 'table'[cost] ),
        'table'[Channel] <> "Disty"
    )
RETURN
    other + disty

Will this work for you?

[Syk]

Now you have a solution for a column or a measure!