Columns visual across report formating

[learner03]

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)

[learner03]

@bolfri I have the original file now and the output report as well. Can I share that with you in Inbox?

[bolfri]

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

[learner03]

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]

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]

@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]

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]

@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]

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]

@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]

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:

[learner03]

@bolfri You have done correctly, and yes the numbers are different to the input tables, just to show you the calculation, I had put random numbers in the output table. I wanted to show you the formula used in the calculated columns.
For example if youse see cell Y57, you will see Month's cover calculation as=Available Qty/ Average of past 6 months Sales.(I have used random numbers in Sales column, just to show you calculation)

Similarly, for Closing projection, you will see formula on the cell.

Hope it makes sense, happy to answer any question.

[learner03]

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

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

[learner03]

@bolfri I have the orignal data and report plan with formulas. Can I send you that ? or meantime can you send the PBIX of the above working that you ave done. Thanks