Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to Solution.
@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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |