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

 

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".

learner03's avatar
learner03
Post Partisan
3 years ago

bolfri Also How can I get below total line to be Sumproduct with the Qty in individual columns*Sell price and not just total of that column- So that the total is the $ value and Quantity is in the columns. 
In addition to this, can I still do conditional highlighting or create additional calculated measure columns in this view-For example, adding addional column to see closing projection for the coming months with the formula= On Hand-forcast+stock due or addng months cover column based on= Stock On Hand/Average of 6 months Sales and doing highlights based on if Months cover >4 ,red etc Can I add such addional columns?

 

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    Can you prepare an Excel file with the expected results so I can understand where do you want and do not want totals and some highlights? You can share here a screenshot of an idea, so I can understand what do you want to achive.

    • learner03's avatar
      learner03
      Post Partisan
      3 years ago

      bolfri Please let me know if you can access this file. I have added calculated colums at the end for MOnth cover and Closing projection as a sample and the formulas are on the cells. There will be more calculated columns in addition but want to see how to add calculated clums in the output view. Also added the highlighting for months cover -if it is less than 5 then green and more than 10 then red. Also, if closing projection is negetive number than red.

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        Hi learner03,

        Your excel file has different values than the based data, but it will be easier for me if we would talk about same numbers.

         

        We need to figure out why dose differences appear, so the calculated columns would work perfect:

         

        1. Column D - Available Qty: you put here numbers: 30, 100, 80 & 500, but non of the item has such Available Qt in thier data. How is it calculated?

        2. Column F-J - Stock Due: How did you calculated that? I used a measure that was Sales * Item price (row calculations), but we have different numbers, so I know that I made something wrong. Can you put some formula here so I will check where is difference?

         

        3. Columns L-Q - Sales: Even here you have different numbers (without any formula), but based on the data I have different results.

        4. Columns S-X - Forecast: Same here. You've provided a sample data with forecasting value, but on the Output table it seems like it's forecast based on formula, not the data. Forecast for Jan 2023 is ok for me, but I need to know how is is calculated for future months. Provide the formula OR the correct input data for Forecast table.

         

        5. Creating additional Header row, Column(s) based on different columns is possible the same way as the previous ones.

         

        6. Due to fact that I didn't create new columns I will show you how to do the conditional formating on forecast field:

        Conditional formating =
        SWITCH(SELECTEDVALUE('Custom Columns'[Header Name]),
        "Forecast",
            SWITCH(TRUE(),
                [Forecast] > 500, "Green",
                [Forecast] < 300, "Red"
            )
        )
         
        Results: