IF specific value, then calculate with other value

[JG_DOT]

Hi everyone,

I thought this was going to be fairly simple, but i guess not.

I have a fact in my fact table that i want to change, lets call it Data[Sales].

I have a dimension table with markets,

lets say it looks like this:

Markets_DIM
Markets
Market 1
Market 2

Market 3

Market 4

I then want the measure to do the following:

if "Market 1" then show data[sales] sum for "Market 2". If any other value than "Market 1", then use that value to sum Data [Sales].

 Basically I really just want the measure to show market 2's value under market 1 for this particular fact.

this is the data now:

Market           Sales

Market 1        

Market 2        100

Market 3        200

Market 4        300

i want the measure to show

Market           Sales

Market 1        100

Market 2        100

Market 3        200

Market 4        300

My data model:

Hope it makes sense,
Thanks!

[FreemanZ]

hi @JG_DOT ,

try like:

1. plot the slicer with a unrelated calculated table like:

Markets_slicer

=VALUES(Markets_dim[Markets])

2. plot visuals with a new measure like:

Sale2=

VAR _a=

SELECTEDVALUE(Markets_slicer[Markets])

VAR _b =IF (_a= "Market 1", "Market 2", _a)

VAR _result =

CALCULATE(

    [sales],

    Markets_dim[Markets] = _b

)

RETURN _result

[JG_DOT]

Hi! 
thank you very much for taking the time.

This is what I tried myself and I can't seem to get it to work.

Market 1 have blank values for data[sales] in my data, and it doesn't show the data for Market 2 as I want to with this solution unfortunately. 
just to be clear, this is what the data looks like:

Market           Sales

Market 1        

Market 2        100

i want the measure to show

Market           Sales

Market 1        100

Market 2        100

I can actually see now that it doesn't necessarily depend on the slicer, I really just want the measure to show market 2's value under market 1 for this particular fact.

 What am I doing wrong? ☹️

[DataNinja777 · Accepted solution]

Hi @JG_DOT ,

You can achieve your desired output by writing the two measures below:

Market2Sales = 
CALCULATE(
    [Sales value],
    ALL(Market),
    Market[Market] = "Market 2"
)

Then, to have Market 1 basically show Market 2's number, use the measure below:

Market 1 Adjusted Sales = 
sumx(Market,if(Market[Market]="Market 1",
    [Market2Sales],blank()
)
)

You can combine the [Sales value] measure with the second measure above to get your desired output.

I have attached an example pbix file for your reference.

Best regards,

[JG_DOT]

Hi! Thank you very much for taking the time.

It is not exactly what i'm looking for.

I want the values for all markets in the same collumn, so Market 1 should have Market 2s value, Market 2, 3 and 4 should have their own values - all in the same collumn. 

Besides that i really cant get you measures to work either, i just get blank values for measure 2...

[DataNinja777]

Hi @JG_DOT ,

It is very straightforward to produce your required output once Market 1 is populated with Market 2's value 😉.  

To spell it out, it can be produced by the following measure, which generates the visualization shown below.

Required output = [Sales value]+[Market 1 Adjusted Sales]

You mentioned that your output came out blank, but I wasn't sure which formula you were using, because my output seems to be coming out OK without producing blank results. Please check the attached pbix file for your reference.

Best regards,

[JG_DOT]

Hi @DataNinja777 

True, i was so focused on keeping the #measures down that i didn't think any further 😀

 Anyways, i used your tips and tricks to make this measure that does the job, so thank you very much - i used way to much time on this and you saved me!

Sales (Market 1-2) = 
IF(SELECTEDVALUE(Markets_DIM[Markets]) = "Market 1",
CALCULATE(
    SUM(Data[Sales]),
    ALL(Markets_DIM[Markets]),
    Markets_DIM[Markets] = "Market 2"),
CALCULATE(
    SUM(Data[Sales])
))