Multiply by a distincs number

Hey,

you can use the following approach.

Use Power Query (meaning the Query Editor) in Power BI to add an Index Column that starts by 1, for this reason it's necessary to order the table accordingly, and then add the Index column.

After this you can create a calculated column like so

newColumn = 
SWITCH(TRUE()
  ,'yourTablename'[nameoftheindexcolumn] <= 350, 18.6
  ,AND(
    'yourTablename'[nameoftheindexcolumn] > 350
    'yourTablename'[nameoftheindexcolumn] <= 450)
  , 20.4
  , ...
)

Hopefully this gets you started

Regards

Tom

---

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?

---

pablors

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.