Power BI Table w/ Slicer Where Other Columns Adjust Parameters Based Off Selection

0
votes

I have the following Power BI table example for an operating expense report that uses a slicer to filter the first column named "Actual". This is to see the operating expenses for one month compared to the budget figures for the year. It also compares the year-to-date and annual figures. How can I create dynamic columns that change based on the slicer selection? These additional columns are not shown in the pic below but included in the last pic. The Budget column below was just created as an example to show what it should look like.

I set up a star schema with several tables shown below. There's only one expense fact table used and the slicer only works for the first column as previously stated but I need all the other columns to use different parameters and adjust based off what's selected in the slicer. The last image is an overview of the info and the parameters for each column. I tried creating new columns with measures for the budget to see if I can get that going but can't figure out how to make it adjust with the slicer selection.

I'm not sure if I should be using separate queries for each column or can this be done using the one expense table. Hope this isn't too confusing. Please let me know if more info is needed.

enter image description here

enter image description here

enter image description here

1
powerbidax

1 Answers

0
votes

If I understood what you wanted correctly I think I solved your problem.

I was able to create the following:

enter image description here

I did not use all values since I did not want to type everything, if you provide some test data it is easier to replicate you dashboard.

This matrix (so not table) allows you to filter for Date (if you so desire, you can always show all date's in the matrix) Book and AccountTree.

The way this is done is by putting the address column in the ROWS for the matrix, Putting the Date column in the COLUMNS of the matrix and putting your values (actual, budget, variance) in the values of the matrix.

For the date is used days, since it was easier to type. You can always use weeks, months, quarters or years.

For this to work you have to create the following relationships:

enter image description here

Hope this helps.

If not, please provide test data so it is easier to try and solve your problem.