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
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:

 

  • learner03's avatar
    learner03
    Post Partisan
    3 years ago

    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.