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 A Jan-23 400 B Dec-22 500 C Jan-23 322 D Feb-22 544 E Feb-22 355 F Nov-22 766 G Oct-22 878 H Dec-22 675 I Sep-22 456 J Nov-22 763 A Dec-22 45 A Nov-22 87 B Oct-22 45 B Sep-22 35 D Jan-23 87 C Dec-22 56 C Nov-22 98 D Oct-22 34 E Dec-22 567 E Nov-22 876 E Oct-22 23 F Dec-22 54 F Oct-22 67 G Nov-22 9 G Dec-22 54 G Jan-23 34 2) Stock Due Item Month Due Quantity Due A Feb-23 233 B Feb-23 433 C Mar-23 567 D Apr-23 543 E May-23 876 F Jan-23 456 A Jan-23 345 B Mar-23 654 D Mar-23 765 K Mar-23 322 L Feb-23 344 3) Sell Price Item Price A 1.1 B 2.1 C 3.2 D 2.3 E 1.4 F 1.5 G 4.2 H 3.4 I 5.2 J 2.5 K 3.2 L 3.4 4) Current Stock Item Stock On Hand Committed Qty Available Qt Back ordered A 200 12 188 222 B 433 43 390 543 C 234 34 200 54 D 65 23 42 345 E 223 43 180 675 F 765 12 753 345 G 345 34 311 653 H 653 54 599 67 I 789 24 765 543 J 45 43 2 657 K 234 12 222 456 L 654 54 600 45 5) Forecast Item Month Forecast Qty A Jan-23 200 B Jan-23 244 C Jan-23 544 D Jan-23 245 E Jan-23 324 F Jan-23 543 G Jan-23 654 H Jan-23 765 I Jan-23 123 J Jan-23 432 K Jan-23 555 L Jan-23 666 A Feb-23 444 B Feb-23 222 D Feb-23 333 E Feb-23 123 F Feb-23 456 C Mar-23 435 D Mar-23 234 E Mar-23 765 A Apr-23 124 B Apr-23 654 C Apr-23 123 D Apr-23 645 OUTPUT Output Stock Due Sales Forecast Item SOH Committed Qty Available Qty Back ordered Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jan-23 Dec-22 Nov-22 Oct-22 Sep-22 Aug-22 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 A B C . $Amount= SUMPRODUCT(Sel price*Jan-23 Qty Column) $Amount= SUMPRODUCT(Sel price*Feb-23 Qty Column)

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

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.

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

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

Forum Discussion

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-

Sales

Item	Month	Quantity

A	Jan-23	400
B	Dec-22	500
C	Jan-23	322
D	Feb-22	544
E	Feb-22	355
F	Nov-22	766
G	Oct-22	878
H	Dec-22	675
I	Sep-22	456
J	Nov-22	763
A	Dec-22	45
A	Nov-22	87
B	Oct-22	45
B	Sep-22	35
D	Jan-23	87
C	Dec-22	56
C	Nov-22	98
D	Oct-22	34
E	Dec-22	567
E	Nov-22	876
E	Oct-22	23
F	Dec-22	54
F	Oct-22	67
G	Nov-22	9
G	Dec-22	54
G	Jan-23	34

Stock Due

Item	Month Due	Quantity Due

A	Feb-23	233
B	Feb-23	433
C	Mar-23	567
D	Apr-23	543
E	May-23	876
F	Jan-23	456
A	Jan-23	345
B	Mar-23	654
D	Mar-23	765
K	Mar-23	322
L	Feb-23	344

Sell Price

Item	Price

A	1.1
B	2.1
C	3.2
D	2.3
E	1.4
F	1.5
G	4.2
H	3.4
I	5.2
J	2.5
K	3.2
L	3.4

Current Stock

Item	Stock On Hand	Committed Qty	Available Qt	Back ordered

A	200	12	188	222
B	433	43	390	543
C	234	34	200	54
D	65	23	42	345
E	223	43	180	675
F	765	12	753	345
G	345	34	311	653
H	653	54	599	67
I	789	24	765	543
J	45	43	2	657
K	234	12	222	456
L	654	54	600	45

Forecast

Item	Month	Forecast Qty

A	Jan-23	200
B	Jan-23	244
C	Jan-23	544
D	Jan-23	245
E	Jan-23	324
F	Jan-23	543
G	Jan-23	654
H	Jan-23	765
I	Jan-23	123
J	Jan-23	432
K	Jan-23	555
L	Jan-23	666
A	Feb-23	444
B	Feb-23	222
D	Feb-23	333
E	Feb-23	123
F	Feb-23	456
C	Mar-23	435
D	Mar-23	234
E	Mar-23	765
A	Apr-23	124
B	Apr-23	654
C	Apr-23	123
D	Apr-23	645

OUTPUT

Output

Stock Due

Sales

Forecast

Item

SOH

Committed Qty

Available Qty

Back ordered

Jan-23

Feb-23

Mar-23

Apr-23

May-23

Jan-23

Dec-22

Nov-22

Oct-22

Sep-22

Aug-22

Jan-23

Feb-23

Mar-23

Apr-23

May-23

Jun-23

$Amount= SUMPRODUCT(Sel price*Jan-23 Qty Column)

$Amount= SUMPRODUCT(Sel price*Feb-23 Qty Column)

Need Help

14 Replies

bolfri
Solution Sage
3 years ago
What have you already tried to do and where are you stuck?
- 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
    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
Post Partisan
3 years ago
bolfri I have the original file now and the output report as well. Can I share that with you in Inbox?

Forum Discussion

Columns visual across report formating

14 Replies

Related content

Formatting Matrix Visuals

Formatting Visual Calculations

Matrix visual - Formatting (rows format differently)

3D Format Column Chart

Issue resizing/formatting a Card visual.