Forum Discussion

planc7's avatar
planc7
Icon for Helper I rankHelper I
2 years ago

Virtual Tables and Columns not working as expected

Hi all, I'm working on for a week but I wasn't able to come up with a solution: The following table is the output of a variable table, where white columns are the original ones (phisical table) whi...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

planc7 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • planc7's avatar
    planc7
    Icon for Helper I rankHelper I
    2 years ago

    Thank you for your reply.

    Unfortunately I wasn't able to find any solution in the forum.

    Anyway, here below the sample of data.

    Essentially, I need to create a virtual column (Actual Planned PY) that repeats Actual/Planned number for each row with the same @Key but only for the next year.

     

    I manage it with a measure, and the measure works, but I need to insert the new column (Actual Planned PY) in a matrix, and here is the point where I have the problem

     

    Here the measure used to recreate the Virtual Table and the Column needed (Actual Planned PY)

     

     
    VAR _Table = 
            ADDCOLUMNS(
                'PHISICAL_TABLE',
                "@Key",
                    IF(NOT ISBLANK(PHISICAL_TABLE[Actual/Planned]),
                    PHISICAL_TABLE[Year]+1&FORMAT(PHISICAL_TABLE[Week],"00")&'PHISICAL_TABLE'[Brand]&'PHISICAL_TABLE'[Cluster],
                    PHISICAL_TABLE[Key Brand])
                    )
    VAR _Table1=
        ADDCOLUMNS(
            _Table,
            "@Actual/PlannedPY",
                IF(ISBLANK(PHISICAL_TABLE[Actual/Planned]),
                CALCULATE(
                    MAX ( PHISICAL_TABLE[Actual/Planned] ),
                    FILTER (
                        _Table,
                        [@Key] = EARLIER ( [@Key] )))))
    RETURN
    SUMX(_Table1, [@Actual/PlannedPY])
     
    Just for information, this is the table output of variable _Table1:

     

     
    The problem encountered is the following: on left the matrix that I actually got doing this formula, on the right the expected one.
    Thank you in advance!

     

     
    YearWeekBrandClusterKey BrandActual/PlannedYear&WeekYear+1
    202351XX2024-AA202351XX2024-AA42023512024
    202451XX2024-AA202451XX2024-AA 2024512025
    202350XX2024-AA202350XX2024-AA42023502024
    202450XX2024-AA202450XX2024-AA 2024502025
    202349XX2024-AA202349XX2024-AA52023492024
    202449XX2024-AA202449XX2024-AA 2024492025
    202347XX2024-AA202347XX2024-AA92023472024
    202447XX2024-AA202447XX2024-AA 2024472025
    202345XX2024-AA202345XX2024-AA122023452024
    202445XX2024-AA202445XX2024-AA 2024452025
    202344XX2024-AA202344XX2024-AA132023442024
    202444XX2024-AA202444XX2024-AA 2024442025
    202343XX2024-AA202343XX2024-AA192023432024
    202443XX2024-AA202443XX2024-AA 2024432025
    202342XX2024-AA202342XX2024-AA172023422024
    202442XX2024-AA202442XX2024-AA 2024422025
    202341XX2024-AA202341XX2024-AA142023412024
    202441XX2024-AA202441XX2024-AA 2024412025
    202340XX2024-AA202340XX2024-AA72023402024
    202440XX2024-AA202440XX2024-AA 2024402025
    202339XX2024-AA202339XX2024-AA102023392024
    202439XX2024-AA202439XX2024-AA 2024392025