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: tbl_Date (col: Date) tbl_Delivered (col: Del qty) tbl_Forecast (col: Fcst qty)   Step 1 - bring these columns t...
Need Help
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.

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      Share the download link of your PBI file.

      • 1001's avatar
        1001
        Resolver II
        3 years ago

        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.