TRICKY - Table creation in Power BI Show 2020 Year End and 2021 Forecasted Values

[Anonymous]

Hi Experts

See links to Excel Table which need recreating in Power BI and Sample Power BI File

Key: 

CM = Current Period

PY = Previous Year

BBP = Budget

PM = Previous Month

FACT Table = PL

Main Table = Logistics

Measure with issues not working = _v_Act2020

I am trying to Populate the data into my table in Power BI to get the same results as show in the sample excel file. I have written the DAX Measure based on the filters on the page - but no luck - cannot see my error.

I should be able to filter the data based on Region and Periods as per filters...

Excel Sample File

https://www.dropbox.com/scl/fi/qe5z8taafa5t4208nrnh8/ExcelSample.xlsx?dl=0&rlkey=igs7wpbtpn9erbxo0gi... 

Power BI Sample File

https://www.dropbox.com/s/6t4w9d77o70av9u/PLP_Test_1.pbix?dl=0 

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Have you checked my attached pbix file？

Yes ,you can remove the Reporting Headers Column and just Use New Reporting headers but if so, Grpid column can't be removed.

As a workaround ,you can shorten the column width to 0 to hide it (you need to turn off the word wrap option)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi, @Anonymous 

I'm not certain that I'm understanding you correctly.

Try to add a new measure as below:

Measure1 =
VAR a =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]
                IN {
                "Inbound Freight",
                "Duty",
                "Warehousing - Fixed",
                "Warehousing - Variable",
                "SC&L People",
                "Outbound Freight"
            }
        ),
        [__v_Act2020]
    )
VAR b =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]
                IN {
                "Inbound Freight",
                "Warehousing - Fixed",
                "Warehousing - Variable",
                "SC&L People",
                "Outbound Freight"
            }
        ),
        [__v_Act2020]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Logistics_[Reporting_Headers] ),
        "Total Logistics", a,
        "Total Logistics (excl Duty)", b,
        [__v_Act2020]
    )

 

(By the way , I have checked the option 'show items with no  data' for some fields.)

Check the attached pbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Eason Team - Excellent / Amazing feedback.... Can we remove the Reporting Headers Column and Just Use New Reporting headers???? and still include the Highlighted as per above image???

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Have you checked my attached pbix file？

Yes ,you can remove the Reporting Headers Column and just Use New Reporting headers but if so, Grpid column can't be removed.

As a workaround ,you can shorten the column width to 0 to hide it (you need to turn off the word wrap option)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Team Eason....Excellent - One last question how can i group/sum the  Reporting Headers so i only show one Line for Inbound Freight, Duty, SC&L People and so on...

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Create a  new measure as below:

Measure2 = 
VAR _external =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]="External Sales"
        ),
        [__v_Act2020]
    ) 
VAR _Duty =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]="Duty"
        ),
        [__v_Act2020]
    )
VAR _Inbound_Freight =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]=
                "Inbound Freight"
        ),
        [__v_Act2020]
    )

VAR _Warehousing_Fixed =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]=
                "Warehousing - Fixed"
        ),
        [__v_Act2020]
    )

VAR _Warehousing_Variable =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]=
                "Warehousing - Variable"
        ),
        [__v_Act2020]
    )
VAR _SC =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]=
                "SC&L People"
        ),
        [__v_Act2020]
    )
VAR _Outbound_Freight =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]=
                "Outbound Freight"
        ),
        [__v_Act2020]
    )

VAR _Total_Logistics =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]
                IN {
                "Inbound Freight",
                "Duty",
                "Warehousing - Fixed",
                "Warehousing - Variable",
                "SC&L People",
                "Outbound Freight"
            }
        ),
        [__v_Act2020]
    )
VAR _Total_Logistics_excl_Duty =
    SUMX (
        FILTER (
            ALL ( Logistics_ ),
            Logistics_[New_Reporting_Headers]
                IN {
                "Inbound Freight",
                "Warehousing - Fixed",
                "Warehousing - Variable",
                "SC&L People",
                "Outbound Freight"
            }
        ),
        [__v_Act2020]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( Logistics_[New_Reporting_Headers] ),
        "External Sales",_external,
        "Duty",_Duty,
        "Inbound Freight",_Inbound_Freight,
        "Warehousing - Fixed",_Warehousing_Fixed,
        "Warehousing - Variable",_Warehousing_Variable,
        "SC&L People",_SC,
        "Outbound Freight",_Outbound_Freight,
        "Total Logistics",_Total_Logistics,
        "Total Logistics (excl Duty)", _Total_Logistics_excl_Duty,
        [__v_Act2020]
    )

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you sir to you are you're team for the amazing feedback - god Bless.

i have posted the last part of the question....
https://community.powerbi.com/t5/Desktop/Add-Percentage-Values-based-on-External-Sales/m-p/1683115#M...

[v-easonf-msft]

Hi, @Anonymous 

I did not see the corresponding pbix  file, the link seems to have expired.

Can you share your sample again?

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Team Eason...

You can  add the measure AE 2020 FY Actuals in the logistics Table to Satndard Table Visual in POwer BI, This give me some of the data points i neeed less the elements in the previous email.

[Anonymous]

Hi Eason

See new Link: https://www.dropbox.com/s/npy2jxnen9l7ig7/PLP_Test_2.pbix?dl=0 

Heads Up:

1.Only look at the Logistics Table and The PL Table and The Dim Hirarachy 

2.Logistic Table Relationship with P&L via GrpID and SNO 

3. Z22 and Z23 dlo not exit in the P&L Table - we need to create these row level summming formula using dax. 

Maths

Total Logistics = Inound Freight + Duty + Warehousing (fixed+variable) + SC&L People + outbound freight

Total Logistics (excel Duty) = is the above less duty

any question drop me a message