Use DAX to add rows to table and use average of existing data as value for new rows

[learner4eva]

How do I use DAX to create a new table with additional rows? The value in the new rows will be using average of the existing rows for each category (version). 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below attached pbix file and the DAX formula for creating a new table.

I believe you have a Calendar Table, like the attached sample pbix file.

NewTable = 
UNION( Data, 
ADDCOLUMNS (
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    SUMMARIZE ( Data, Data[Version], Data[WeekNum] ),
                    "@rowcount", COUNTROWS ( FILTER ( Data, Data[Version] = EARLIER ( Data[Version] ) ) ),
                    "@weekoffsetnumber",
                        MAXX (
                            FILTER ( 'Calendar', 'Calendar'[WeekNumber] = Data[WeekNum] ),
                            'Calendar'[WeekOffset]
                        )
                ),
                "@newweekoffsetnumber", [@weekoffsetnumber] + [@rowcount]
            ),
            "@newweeknumber",
                MAXX (
                    FILTER ( 'Calendar', 'Calendar'[WeekOffset] = [@newweekoffsetnumber] ),
                    'Calendar'[WeekNumber]
                )
        ),
        Data[Version],
        [@newweeknumber]
    ),
    "@valueavg",
        AVERAGEX (
            FILTER ( Data, Data[Version] = EARLIER ( Data[Version] ) ),
            Data[Value]
        )
)
)

[learner4eva]

Thank you Jihwan, this works very well!

[amitchandak]

@learner4eva ,

union(Table,
crossjoin(summarize(filter(Table, Table[Week] >=202140 && Table[Week] <=202144), [Week]), AvergaeX(summarize(filter(Table, Table[Week] >=202140 && Table[Week] <=202144), "_1", Average(Table[Value])),[_1])),

crossjoin(summarize(filter(Table, Table[Week] >=202145 && Table[Week] <=202145), [Week]), AvergaeX(summarize(filter(Table, Table[Week] >=202145 && Table[Week] <=202145), "_1", Average(Table[Value])),[_1]))
)

[learner4eva]

Hi Amit,

Thank you for the fast reply..

Problem is I have about 1000 versions (1st column).. the screenshot is only showing 2 out of 1000 versions..
I also have a calendar table that allows me to lookup the weeknum.

Can you advise? Thank you