Forum Discussion

learner03's avatar
learner03
Post Partisan
3 years ago

Columns visual across report formating

I have few tables with dififfent data and all needs to be linked to give below sort of output. Please guide- 1) Sales           Item Month Quantity       ...
Need Help
bolfri's avatar
bolfri
Solution Sage
3 years ago

What have you already tried to do and where are you stuck?

  • learner03's avatar
    learner03
    Post Partisan
    3 years ago

    I have linked all the tables but not getting the way  in which I need the output..like slaes columns month-wise acroass and then Stock due columns month-wise across from another table and then forecast columns across month-wise from agaiin another tabl and also the sumtotal calculation under each column by multiplying by sell price table.

    • bolfri's avatar
      bolfri
      Solution Sage
      3 years ago

      You're missing one table dim_calendar that will helps you working with multiple dates:

       

      dim_calendar = 
CALENDAR(
    MIN(
        MIN(FIRSTDATE(Sales[Month]),FIRSTDATE(Forecast[Month])),
        FIRSTDATE('Stock Due'[Month Due])
    )
    ,
    MAX(
        MAX(LASTDATE(Sales[Month]),LASTDATE(Forecast[Month])),
        LASTDATE('Stock Due'[Month Due])
    )
)

       

       

      You can consider Sell Price Table as a dimention table and in your visuals the [Item] column should be from this table.

       

      Your relationship should be like this:

       

      Current Stock Measures:

      SOH = SUM('Current Stock'[Stock On Hand])
      Commited Qty = SUM('Current Stock'[Committed Qty])
      Available Qt = SUM('Current Stock'[Available Qt])
      Back ordered = SUM('Current Stock'[Back ordered])
       
      Results: 
       
       
      Forecast Measures:
      Forecast = SUM(Forecast[Forecast Qty])
       
      Results:

       

      Quantity Measures:

      Quantity = SUM(Sales[Quantity])
       
      Results:

       

      Price = AVERAGE('Sell Price'[Price])
      Quantity with Price =
      SUMX(DISTINCT('Sell Price'[Item]),
          [Quantity] * [Price]
      )
       

       

      That's it. With this model you can simply build all you want. 🙂

      • learner03's avatar
        learner03
        Post Partisan
        3 years ago

        bolfri Thanks for this. But, how will I merge all the visual in one table visual as I need in my Output. The above is seperate table visual for each.