Forum Discussion
Multiply by a distincs number
Microsoft Employee
pablors wrote:
Hello everybody,
I have a table that look like this
DATE ITEM
01/09/2017 15
02/09/2017 26
03/09/2017 21
04/09/2017 19
05/09/2017 29
...
...
What i want to get is a measure that multiply del first 350 item by a determined number (18,6 in this case), from 351 to 450 by 20,4 and more than 450 by 22.
Then, if i have 486 item right now, the total would be 350*18,6=6510; 100*20,4=2040 and 36*22=792, with a global total = 9342
Does anyone know how to create a measure like this?
You could try to create a measure as below.
Measure =
VAR RANK_ =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[date], "item_", SUM ( 'Table'[item] ) ),
"rank", RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[date] ) ),, ASC, DENSE )
)
RETURN
SUMX (
RANK_,
SWITCH (
TRUE (),
[rank] <= 350, [item_] * 18.6,
[rank] <= 450, [item_] * 20,
[item_] * 22
)
)
See my demo below. In my demo, I set the rank range 0-2(*2), 2-4(*4), >4(*5). See more details in the attached pbix file.
Hello Eric, and thanks for your answer.
It doesn´t work in this case.
I think it´s because there aren´t enough items in each to do a distinct multiply. I mean, my table is like this:
DATE ITEM
01/09/2017 15
01/09/2017 21
02/09/2017 9
03/09/2017 13
04/09/2017 20
04/09/2017 11
... ...
The problem is that the multiply has to be done with the total, beacuse every row doesn´t acumulate enough items to reach the second level. If the stages were lower, it will work, but it doesn´t.
In my PBI I have 578 item, and i get 14.218,80, this is 578 * 24,6. This is using, your measure, Eric.
This 24,6 is the first stage, the second would be 30 and the third 34.
The steps explained are these:
<=550 * 24,6
>550 and <=650 * 30
>650 * 34
I really apreciatte your answers.
Sorry for my english.
Regards.