Forum Discussion
Hide column matrix
Hi.
I have created measures for last year and YTD values, and are showing them in a matrix. Everything is working ok. But i would like to show the actual year in the column header. The easiest way to do this i was thinking to just drag "Year" from the date table to the matrix column. This works fine in "analyse in excel" with the same data in pivot, but for some reason the matrix in power BI shows empty columns:
This is the matrix before year column is added, i put year-month in the rows to show where data is showing:
Here is same table after i drag year to columns, as you can see it shows both last year and YTD for both 2020 and 2021, even though there obviously is not data in thos measures in those years:
In excel it shows like this, which is what i want:
Since it works in excel i was hoping there was an configuration in power BI which i have missed, but i cant figure out where 😐
The measures are calculates as follows:
YTD =
CALCULATE(
[total],
FILTER(Periode, Periode[Year] = SELECTEDVALUE(x_LookupTable_periode[Year])),
FILTER(Periode, Periode[Month] <= SELECTEDVALUE(x_LookupTable_periode[Month]))
)
last year =
CALCULATE(
[total],
FILTER(Periode, Periode[Year] = SELECTEDVALUE(x_LookupTable_periode[Year])-1)
)
4 Replies
- SivaManiResident Rockstar
It is a limitation in Power BI. There is not straightforward option/feature to make the column label dynamic.
As workaround,
Considering you fixed the number of columns (Last Year and YTD).
- Create 2 measures to get the last year and current year
- Created 2 card visuals and place them on Matrix visual (on top of the columns)
- Make the required UI changes
Note: when you export data from the matrix visual, it will still have last year and YTD as column names
Thanks,
Siva Mani
- HanspwHelper I
Sounds strange to me that this dont work in power BI when it works in excel pivot, but i guess you are right.
The workaround could work but is not as dynamic as i would like. I have more columns lets say budget, forecast etc, and i would need to change the location of the card visuals dependens on where the columns are. Also i may remember that the columns change location when you expand/drill on rows with longer text, so i guess that would offset the card visual from the acual column?