Static Row Headers with calculated row values

Question

newer user ro power bi desktop coming from Qlik Sense. In Qlik I was able to create a table with what I call static row headers and a row of calculated fields. Here is what I am trying to accomplish, this is just a made-up sample visual table.

          Current Year     Prior Year     Year Over Year Change
Returns       50                 40                10
Sales        125                105                20 
Shipped       45                 30                15

The first column Returns, Sales, and Shipped are static values that just describe the row of data. The other 3 columns are calculated values. Any help would greatly be appreciated.

apech · Accepted Answer · 2023-01-10 23:46:47Z

Unfortunately I have no way to calculate the year-over-year change if you don’t provide the previous year as well. Assuming you can provide the previous year´s data, you can just add a DAX calculated column containing the subtraction of Current Year - Previous Year. Quite simple, inside the modeling view of PBI, you will find a button to add a column to an existing table, here is a basic example of what you need:

‘Facts’.[YoY Change] = 
‘Facts’.[Current Year] - ‘Facts’.[Previous Year]

Alternatively you could perform the calculation of the column in the PowerQuery phase and increase the size of your data model with the positive trade-off that your DAX Engine won’t take as long to perform all it`s calculations.

Just two more quick tips:

The first column will also be a named column in your report´s table, maybe “Orders”.
Ideally your data model would store the facts of returns, sales, and shipped in separate fact tables, as they are events that take place independently from one another.

Hope this helps you somehow…

Collectives™ on Stack Overflow

Static Row Headers with calculated row values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related