Increase Sales Value upto a Number

[dilawer5]

Hi,

I need to increase values of salestable(value column) upto a certain total value of 100,000. Please see below example.

 

[Chihiro]

So you need a goal seek?

Hmm, I'm not aware of any DAX that can perform it. Then again, I haven't really looked into goal seeking in DAX.

You could... use R if in PowerBI, I suppose.

 

[dilawer5]

Dear,

To make it simpler, Lets break it in two steps.

1. Calculate a factor by dividing :=Divide(100,000,Calulate(Sum(Table, Value),Filter(Product=ProductA),Datesbetween(Date,StardDate,Enddate))

for example outcome is 450%

2. Then Calulate(Sum(Table, Value),Filter(Product=ProductA),Datesbetween(Date,StardDate,Enddate))*450%

That I tried but ended up 100,000 from top to bottom all rows.

 

[Chihiro]

Can you upload sample model for us to test things on?

 

[dilawer5]

Can you upload sample model for us to test things on?

Yes, Here is the sample file.

 

[Chihiro]

But is this complete sample of model? Looking at your attempt I assume you have date dimension table. Is that correct?

 

[Chihiro]

Here's how it can be done without date dimension table.

1. Factor
=10000/CALCULATE(SUM(salesvalue[Value]),FILTER(ALL(salesvalue),salesvalue[Time]<=201703))

2. Multiplied value
=SUMX(Filter(salesvalue,salesvalue[Time]>=201701&&salesvalue[Time]<=201703),salesvalue[Value]*[Factr])

See attached.

 

[dilawer5]

Here is the example file