Parameter value as a Target/benchmark column

[quintans1]

Hello, I am trying to incorporate a column that is based off a parameter value [Target Ratio] and would like to show the difference in a column, whether for the row or the grand total. For example, if I am showing 3, 7 or however many months of data, I need this target column to be checked against the total.

My screenshot is of what I would like to show in Power BI but using excel as my example. I have arbitrarily entered 8.8, and the “Diff from Target” is what I am hoping to show in Power BI.

https://www.dropbox.com/s/8vgrjxuja35rqhb/sample5.pbix?dl=0 Excel Mockup of what I am hoping to get in Power BI.

[Wilson_]

Hello quintans1,

Looks like you've already created a parameter. Create a Diff from Target measure like:

Diff from Target = [calcRatio] - [Target Ratio Value]

The trickier part is to show it only for the total. The only way I know of to do that is to turn off auto-size width, values text wrap and column header text wrap, then manually reduce the size of the Diff from Target column for all the non-total months to zero. I'm not 100% sure how it interacts if you change the column headers to something else but I think it's fairly safe since you're using calendar months.

This is what I ended up with:

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[quintans1]

This solution helps a lot, but I don't see a way to change the size of the Diff from Target column for all the non-total months to zero unless I do this manually in the visualization. I don't see where in the formatting I can change the size to zero. This is of no concern if I show all the months in a year, but if I am showing something throughout the year that expands (months or quarters), I will need that column to be set to zero so I don't have to manually adjust.Looks fine after I fix this manuallyThe addition of a quarter makes for more manual work.

[Wilson_]

quintans1,

There is unfortunately no way to do it automatically that I know of. Maybe somebody else will come along and teach us both something.

The good news (if you can call it that) is you should only need to manually adjust the column width to zero the first time (ie: if you filter for a previous year and reduce the column width to 0 for all quarters and months then switch back to this year, you shouldn't need to do it again for, say, June 2023). Not ideal but like I said, I don't know of another way to do it, since there's no functionality to only show a measure as a grand total, but not for every column (that I know of or can find).

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)