Custom targets in column chart

[mflaursen]

I am trying to make a clustered bar chart. Two columns for each month. One column is target, the other is actual value. 

The actual values come from a data source consisting of a column of processes. I am summing the number of times a specific process is in the column. This combined with the date column from that data source (all dates from 2018), yields a nice bar chart like this:

I have then made an excel sheet with target values like this:

If I make a column chart of this I get:

It still looks correct. Now, if I combine the two data sources in a stacked column chart I get this:

Each column of the actual value is now the sum of all the values. If I choose average instead of sum, I get 12 identical bars with the average value etc. The visual does not have to be a clustered bar chart, but I get the same result in a line/column chart.

How to fix?

[Anonymous]

I think you don't use a date table, which is why the measure is showing the total of all values, instead of the value per month.

If you don't use a date table yet, you should try it. It is very handy when filtering multiple columns from different tables.

Here is the DAX formula to create such date table (Model > new table):

DateTable = ADDCOLUMNS ( CALENDAR (DATE(2001,1,1), DATE(2020,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "MMMM" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

Then make the relationships between the date table and the relevant tables. When you add the month from the date table, it should show the correct values.

Please let me know if this works.

[mflaursen]

Still does not seem to work 😞 I have connected the "Date" column to the "Lotmovementdate" in the lotmovmentdata and the "Target date" in the Goals table.

[v-shex-msft]

Hi @mflaursen,

I'd like to suggest you change cross filter direction to 'both' for these tables and try again, it seems like power bi can't use current relationship to analysis and gripping your records.

Regards,

Xiaoxin Sheng