• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get the price update factor

Aquila

New Member
Hello, I need to get a table of factors from a table of prices per month and per year. Below I show the Price table and what to get:
230cc2dfa62d

You must get the division of each month and year for the same month but last year. For example the price of the month of January/2015 should be divided by January/2018, January/2016 by January/2018. I need to get a price update factor per month and year. I leave the file in Excel with the formulas used.
Thanks a lot.
 

Attachments

  • Get the price update factor.xlsx
    23.4 KB · Views: 5
Aquila,

Do you mean like this ?
- [K3]= Tabla1[@2015]/Tabla1[@[2018]:[2018]]
- Drag down and across.

The red part is a mixed structured reference: relative by row (@ used) and absolute by column ([2018]:[2018]).

EDIT: sorry for the earlier goof-up. Morning coffee just kicked-in. You'd need to fetch 2018 automatically, so when 2019 arrives, the calculation uses the column 2019?
 
Last edited:
Perhaps this then? [K3]=Tabla1[@2015]/INDEX(Tabla1,,COUNTA(Tabla1[#Headers]))
 

Attachments

  • Copy of Get the price update factor-1.xlsx
    23.4 KB · Views: 4
That's right, your solution is very good, but I've omitted to say I wanted to do it with Dax formulas. I've tried some for I haven't been able to.
 
You don't need DAX formula for this.

Just load the table flattened in PQ into data model.

Create pivot table. Add Months to row label (apply manual sorting the first time).
Then add years into column label. Sum of Price into values field.

Then right click on value field. "Show Value As"->% Of->2018.

See attached.
 

Attachments

  • Copy of Get the price update factor-1.xlsx
    177.4 KB · Views: 2
Thank you very much, very ingenious! This is just the solution that I have to get to, but for the exercise, I have passed only a summary, to focus directly on the problem I have. Now I tell you everything, it happens that I did not want to complicate.
I'm working on Power BI. I need to get a table with the price update factors of a sales table, but only the products "A ", of an ABC classification. To do this classification I have been asked to do so taking into account the amount of sales per product. OK, this has been simple, I have created columns calculated in the Product table, and I have obtained the list of products "A ". From here, the problem arises. It is necessary to create a table with the list of products "A ", valued at cost price per year and per month; This could do with the following Dax formula.

upload_2018-11-21_15-2-48.png

This is the table that I have passed, and from which I must arrive at the solution that you have shown me so well. I must add a column with the factors, in the style of Excel "Show Value As"->% Of->2018
Thanks a lot.
 

Attachments

  • upload_2018-11-21_14-51-22.png
    upload_2018-11-21_14-51-22.png
    87.3 KB · Views: 5
  • upload_2018-11-21_15-2-2.png
    upload_2018-11-21_15-2-2.png
    93.7 KB · Views: 6
  • upload_2018-11-21_15-13-21.png
    upload_2018-11-21_15-13-21.png
    5.1 KB · Views: 5
I wouldn't do transformation in DAX. Unless there's other related table (dimension table) to original format and you don't want to alter it. Even if you do... you can reference original query and make transformation. This avoids issues associated with calculated tables in DAX.

Just do transformation like done in PQ (Query Editor in PowerBI), but add number in front of month name.
Ex: 01 - jan, 02 - feb.

This part can be done in DAX or M. I did it in DAX using following Caluclated Column.
Code:
SortMonth = FORMAT(MONTH(Datevalue([month]&" "& "1," & [Year])),"00") & " - " & [month]

Now create measure to calculate monthly total for 2018 regardless of filter context, using ALLEXCEPT.
Code:
2018Val = CALCULATE(SUM(Table1[Value]),FILTER(ALLEXCEPT(Table1,Table1[SortMonth]),[Year]=2018))

Next, is to add factor calculation.
Code:
%Of2018 = SUM(Table1[Value])/[2018Val]

Then format the measure as %.

Add matrix visual, set Rows to [SortMonth], Columns to [Year] and Values [%Of2018]. Remove Column subtotals from the visual.

upload_2018-11-21_15-44-57.png

FYI - Parallel period will not work without date level grain data with date dimension table.

See attached.
 

Attachments

  • Sample_Aquila.zip
    36.9 KB · Views: 2
Back
Top