Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
planc7
Frequent Visitor

Virtual Tables and Columns not working as expected

‎03-29-2024 04:56 AM

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) while the yellows are virtual columns created with the following measure:

 

VAR _Table1=

    ADDCOLUMNS(

        PHISICAL_TABLE,

        "@Key",PHISICAL_TABLE[Key Brand]

        "@Actual/PlannedPY",

            IF(PHISICAL_TABLE[Cluster]="2024-N1" && ISBLANK(PHISICAL_TABLE[Actual/Planned]),

            CALCULATE(

                MAX (PHISICAL_TABLE[Actual/Planned] ),

                FILTER (

                    PHISICAL_TABLE,

                    [@Key] = EARLIER ( [@Key] )))))

 

planc7_0-1711713031530.png

 

In reality, I have already achieved the purpose with the measure, that is repeating the number in 4th column for each row with the same @Key but only for the next year (highlighted in yellow)

 

But when I try to put @Actual/Planned column in a matrix object with SUMX, Actual/Planned values are not displayed correctly.

RETURN

SUMX(_Table1, [@Actual/PlannedPY])

(I would expect to have the value of @Actual/Planned repeated for the corresponding Year and Week (columns of phisical table), not only just the total row)

planc7_2-1711713347911.png

 

 

PS: i need to manage it with virtual tables and columns, so please don't suggest me to calculate it directly into the phisical table.

 

Thanks in advance!

 

Luca

Labels:
Message 1 of 3
148 Views
0
2 REPLIES 2
Greg_Deckler
Super User
Super User

‎03-29-2024 06:07 AM

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 3
118 Views
0
planc7
Frequent Visitor
In response to Greg_Deckler

‎03-29-2024 07:41 AM

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:
planc7_1-1711723520330.png

 

 
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!
planc7_0-1711723270159.png

 

 
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
Message 3 of 3
104 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All