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.

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

         

        Custom Columns = 
UNION
    (
        //Current Stock
        ROW("Date",BLANK(),"Header Name","Current Stock","Header Index",1,"Column Index",1,"Column","SOH"),
        ROW("Date",BLANK(),"Header Name","Current Stock","Header Index",1,"Column Index",2,"Column","Commited Qty"),
        ROW("Date",BLANK(),"Header Name","Current Stock","Header Index",1,"Column Index",3,"Column","Available Qt"),
        //Sales for Sales * Price
        SUMMARIZE('Sales','Sales'[Month],"Header name","Stock Due","Header Index",2,"Column Index",YEAR('Sales'[Month])*100+MONTH('Sales'[Month]),"Column",FORMAT('Sales'[Month],"YYYY MM")),
        //Sales for Sales
        SUMMARIZE('Sales','Sales'[Month],"Header name","Sales","Header Index",3,"Column Index",YEAR('Sales'[Month])*100+MONTH('Sales'[Month]),"Column",FORMAT('Sales'[Month],"YYYY MM")),
        //Forecast
        SUMMARIZE('Stock Due','Stock Due'[Month Due],"Header name","Forecast","Header Index",4,"Column Index",YEAR('Stock Due'[Month Due])*100+MONTH('Stock Due'[Month Due]),"Column",FORMAT('Stock Due'[Month Due],"YYYY MM"))
    )

         

         

        Results:

         

        Dynamic Value = 
SUMX('Custom Columns',
    SWITCH('Custom Columns'[Header Name],
    "Current Stock",
        SWITCH('Custom Columns'[Column],
            "SOH",[SOH],
            "Commited Qty",[Commited Qty],
            "Available Qt",[Available Qt]
        ),
    "Stock Due",[Quantity with Price],
    "Sales",[Quantity],
    "Forecast",[Forecast]
    )
)

         

         

        Results in the table:

         

        Note that you can addapt this solution to your needs. It react(s) on filtering by dimentions and even calendar. Main issue here i Current Stock Values need to be adapt to remove filters from dim_calendar table to behave as "always show".