Forum Discussion
Columns visual across report formating
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.
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".
bolfri So if the values in Current Stock is 0, will it show that item line, as there can be items where these values are 0 and they have stock due, sales and stock due values? So we need to see that line.
It will show all the items if any of columns has a value. To be sure that we are showing all the Items we can click on Items and select option: Show rekords witout the data, but I am pretty sure that it works without that option.
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?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.
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.