Help creating a table from other tables.

[1001]

Hi All, every DAX example I find on the web is not working. I have three tables:

1. tbl_Date (col: Date)
2. tbl_Delivered (col: Del qty)
3. tbl_Forecast (col: Fcst qty)

Step 1 - bring these columns together into a virtual table.

Step 2 - add 3 calculated columns

1. Del qty - Del fcst = variance
2. Variance / Del fcst = Variance %
3. ABS (Variance)

The reason I'm doing this is because the Matrix table I'm using isn't correctly totalling the ASB Var column. The ABS Var column is giving me the same total result as the Variance column. This is the only way I can figure to resolve the issue. 

Thanks All if you can help. 

[Ashish_Mathur]

Hi,

You should create additional dimension tables (just like you have the Date table) and create relationships (Many to One and Single) from your 2 Fact tables to the newly created dimension tables.  To your visual, drag fields from the dimension tables to visuals/slicers/filters.  These simple measures should then work

DQ = sum(tbl_delivered[Del qty])

FQ = sum(tbl_Forecast[Fcst qty])

Variance = [DQ]-[FQ]

Variance % = divide([variance],[FQ])

ABS variance = if([variance]<0,-[variance],[variance])

Hope this helps.

[1001]

Hi Ashish,

I had in place your suggested relational structure. Applying your ABS Variance DAX changed the Varianbce to positive values but the total is still summing the same the Variance Total. 

The ABS Variance should be a much higher value. This is where I'm having issue.  Thanks.

[Ashish_Mathur]

Share the download link of your PBI file.

[1001]

Sorry Ashish, this is company info, I would be prohibited from making it available. But below is what I'm encountering using a Matrix table. You can see the bottom Totals row, the Var and ABS Var are exactly the same number at 3,846 where the ABS Var total should be 14,530.

[Ashish_Mathur]

Try this measure

ABS var_new = SUMX(VALUES(Data[Product]),[ABS var])

Replace Data[Product] with the table and column name which you have dragged to the row labels of the matrix.

[1001]

Thanks Ashish,  but the issue with this formula is the Var number is a Measure, not a calculated column which SUMX needs to perform. And the Qty Del and Qty Fcst columns live in two separate tables. This is the mess I've run into. Thx.

[Ashish_Mathur]

I cannot help witthout a file.  Simple.

[1001]

Hi Ashish, am creating a duplicate dummy PBI file. Will upload it tomorrow. Many thanks for your help.