Forum Discussion
learner03
Post Partisan
Post PartisanColumns 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
...
bolfri
Solution Sage
Solution Sage
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".
learner03Post Partisan
Post Partisanbolfri 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.
- bolfri
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.