Forum Discussion
Take the first data entry from specific columns
Dear All,
I would like to ask for your help, becasue I was not able to figure out how the following issue could be solved with DAX formula.
I have the table below (the table has several other columns), where you can see in the column the monthly cost of sales for different products.
I would like to calculate the cost of sales increase in the following way.
COS increase = Dec COS divided by the first cost of sales entry
Example
- 1st line: 14,93/14,65
- 2nd line: 8,26/8,01
Is there any Dax formula which could give me back the first entry in a row? (but only in aspect of the specific columns, as the table contains several other colums)
Thank you very much for your help.
Best regards
Feri
viferenc
Agree with YukiK , unpivoting and building your model would be the best approach but if you want to do the calculation based on your existing model, then add a new column to your table as I have shown in the example below, I added just three months but you can include all 12 months.1st Value = DIVIDE( FIRSTNONBLANK( {[Oct COS],[Nov COS],[Dec COS]} , [Value] ), [Dec COS] )
5 Replies
viferenc
Agree with YukiK , unpivoting and building your model would be the best approach but if you want to do the calculation based on your existing model, then add a new column to your table as I have shown in the example below, I added just three months but you can include all 12 months.1st Value = DIVIDE( FIRSTNONBLANK( {[Oct COS],[Nov COS],[Dec COS]} , [Value] ), [Dec COS] )What does each row represent? I was thinking we can upivot the columns into rows, and use something like FIRSTNONBLANK() function. The way the table is set up doesn't seem to be the best way to handle this
Each row represents a product and the row shows the monthly development of the cost of sales.
Not sure how big of your table is, but I'd unpivot it so that the table will have product, month, and value (COS) columns. And from there, you'd be able to leverage functions like FIRSTNONBLANK() and LASTNONBLANK(). If you have sample data to share, I could further investigate what should be done.
Hope this helps!
Hi viferenc ,
You can create an index column and then create a measure similar to the following
Column = CALCULATE(MAX('Table'[Column1]),FILTER(ALL('Table'),[Index]=1))How to add an index column:
https://docs.microsoft.com/en-us/power-query/add-index-column
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
