Forum Discussion

1001's avatar
1001
Resolver II
3 years ago

Help creating a table from other tables.

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. 

 

 

Need Help

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      1001
      Resolver II
      3 years ago

      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.