Power BI - Show all data from table1 in a visual from two many to many related tables when one other field from table2 on the X-axis

user_6512540 0
Viewed 214

I have a key column in two many to many related table and sample representation of data is -

(attaching sample version of the table to get the point across as there are other numerous columns not contributing to this visual)

table 1 -

table 1

table 2 -

table 2

I am making a line graph with date on x axis and the value1 and value 2 on y-axis. The value1 is true for all dates. It is basically a standard target value. Now I want all the value1 summed up to show in my visual as value1 and not just the ones for which I have data on those dates. To explain it better I want 1717 on the graph as well like the total in the table in the following image -

visual -

table 3

Is there a way to do this in power BI? I tried to make a shared dimension of all unique key as a separate table and connecting both the tables to that table but there is no change in visual due to that.

dax powerbi many-to-many database
1 Answers

You can follow these below steps to achieve your required output-

Step-1 Create a custom column in your *table 1 as below-

value_1_sum = 
CALCULATE(
    SUM(table_2[value1]),
    ALL(table_2)
)

Step-2 Configure your line chart as below. Remember, the aggregation for new custom column will be Average as shown in the image

enter image description here

And here below is the final output-

enter image description here

Additional Reference Here below is list of options you will get after right click on the measure name-

enter image description here

user_36523450
Related