Currently I have a pivot table by segment and location based on a dataset of individual orders. Certain locations and/or segments do not have any data and show up as NULL values.
Additionally in the dataset I added individual lines for every location and segment permutation and another column for budget.
I am looking to replace the null values in the pivot table with the values from the budget column.
I trailed off there at the end. Yes I’m not sure why my calculation is not working. In the pivot, I still have null values when I have {Location} as Rows and {Segment} as Columns.
So my date structure are individual orders where with the following fields {ID}, {Location}, {Segment}, {Cost}. I then appended individual rows {Budget} for each {Location} {Segment} to the end of the data. So for example it looks like this
|ID | Location | Segment | Cost | Budget |
|1 | xx | bike | 10 |
|2 | yy | car | 20 |
|3| yy | bike | 15 |
|NULL| xx | bike | NULL | 50 |
|NULL| xx | car | NULL |100 |
|NULL| yy | bike | NULL | 34 |
|NULL| yy | car | NULL | 40 |
So the budget is already aggregated in the data. I’m not sure how to do this on a row level. When I put this data into a pivot, I’m looking to replace NULL or zero values with the budget value. Also current there are not orders for all combination of {Location}, {Segment} but I still have a budget and want it to show.
You can leverage the coalesce function to show budget when cost is null.
However, showing multiple measures under the same pivot column can be confusing to end users.
So, I would recommend adding conditional formatting to the pivot to clearly demarcate the two.