AVERAGEX with a virtual table

Hi folks,
I’ve been working with TE3 and built the query below.

When I run it, TE3 gives me an error “The TOPN function expects a table expression for argument ‘2’, but a string or numeric expression was used.”

I wasn’t using TOPN, so just for giggles, I removed the evaluate statement and then created “TestMeasure” in my pbix. I dropped TestMeasure onto a card and it returned the correct answer. I then attempted to put the measure into a table visual and I get “Can’t display the visual”. Details behind that are in the attached file.
error-details.mcode (28.6 KB)

Any ideas?

/* START QUERY BUILDER */
EVALUATE
//VAR WOperRev = [Weight - Operating Revenue]
//VAR WOperExp = [Weight - Operating Expense]
//VAR WOperInc = [Weight - Operating Income]
VAR weights =
    SUMMARIZECOLUMNS(
        soi[Level 03],
        KEEPFILTERS( TREATAS( { 1 }, captions[Sub Total] ) ),
        KEEPFILTERS( TREATAS( { 0 }, calendar[Month Offset] ) ),
        KEEPFILTERS( TREATAS( { 40 }, captions[Caption ID] ) ),
        KEEPFILTERS( TREATAS( { 8 }, cost_centers[Reporting Company] ) ),
        "WOperRev", [Weight - Operating Revenue],
        "WOperExp", [Weight - Operating Expense],
        "WOperInc", [Weight - Operating Income]
    )
VAR WOperRev = MAXX( weights, [WOperRev] )
VAR WOperExp = MAXX( weights, [WOperExp] )
VAR WOperInc = MAXX( weights, [WOperInc] )
VAR weightTable =
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            soi[Level 03],
            cost_centers[Reporting Company],
            cost_centers[Cost Center],
            chart_of_accounts[Account ID],
            captions[Caption ID],
            //captions[Caption],
            KEEPFILTERS( TREATAS( { 1 }, captions[Sub Total] ) ),
            KEEPFILTERS( TREATAS( { 0 }, calendar[Month Offset] ) ),
            KEEPFILTERS( TREATAS( { 40 }, captions[Caption ID] ) ),
            KEEPFILTERS( TREATAS( { 8 }, cost_centers[Reporting Company] ) ),
            "SubSum", [Subsys03_Sum],
            "BudSum", [Budgets03_Sum]
        ),
        "BudWRev", [BudSum] * WOperRev,
        "BudWExo", [BudSum] * WOperExp,
        "BudWInc", [BudSum] * WOperInc,
        "WRev", WOperRev,
        "WExp", WOperExp,
        "WInc", WOperInc
    )
VAR AvgRev = AVERAGEX( weightTable, [BudWRev] )
RETURN
    AvgRev

Any tips for obfuscating the data if a pbix is needed would be appreciated.

Hi @kaDargo. Here’s @BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
Greg

Greg, thanks for the link.

I’ve anonymized the data and removed the unnecessary stuff. File is a little over 4MB.
AVERAGEX with virtual table.pbix (4.1 MB)

@kaDargo That’s expected SUMMARIZECOLUMNS can not be used in a measure or a calculated column.

1 Like

Ok, is there another way to solve it?

@kaDargo Use SUMMARIZE for grouping + ADDCOLUMNS for adding new columns + CALCULATE/CALCULATETABLE for injecting filter.

I changed it, but evidently I changed it poorly because I’m still getting the same error in TE3.

Code is below.

Any ideas?

/* START QUERY BUILDER */
EVALUATE
VAR weights =
    SUMMARIZECOLUMNS(
        soi[Level 03],
        KEEPFILTERS( TREATAS( { 1 }, captions[Sub Total] ) ),
        KEEPFILTERS( TREATAS( { 0 }, calendar[Month Offset] ) ),
        KEEPFILTERS( TREATAS( { 40 }, captions[Caption ID] ) ),
        KEEPFILTERS( TREATAS( { 8 }, cost_centers[Reporting Company] ) ),
        "WOperRev", [Weight - Operating Revenue],
        "WOperExp", [Weight - Operating Expense],
        "WOperInc", [Weight - Operating Income]
    )
VAR WOperRev = MAXX( weights, [WOperRev] )
VAR WOperExp = MAXX( weights, [WOperExp] )
VAR WOperInc = MAXX( weights, [WOperInc] )

VAR weightTable =
    CALCULATETABLE(
        CALCULATETABLE(
            CALCULATETABLE(
                ADDCOLUMNS(
                    SUMMARIZE(
                        amounts,
                        soi[Level 03],
                        cost_centers[Reporting Company],
                        cost_centers[Cost Center],
                        chart_of_accounts[Account ID],
                        captions[Caption ID],
                        "SubSum", [Subsys03_Sum],
                        "BudSum", [Budgets03_Sum]
                    ),
                    "@BudWRev", [BudSum] * WOperRev,
                    "@BudWExp", [BudSum] * WOperExp,
                    "@BudWInc", [BudSum] * WOperInc
                ),
                captions[Sub Total] = 1 && captions[Caption ID] = 40
            ),
            calendar[Month Offset] = 0
        ),
        cost_centers[Reporting Company] = 8
    )
VAR AvgRev = AVERAGEX( weightTable, [@BudWRev] )
RETURN
    AvgRev

@AntrikshSharma ,
If you have any other advice, I would appreciate the input.
The code below returns 5 columns and 1 row. The last column ([AvgNewBud]) has the value I want.

I put it in a measure and added a RETURN statement of ABS(MAXX(CaptionCompaniesAmounts,AvgNewBud])). If I put this measure in a card, it gives me the correct number.

I added it to a table visual, expecting the same number to be on each row. It doesn’t do that…I get mostly blanks. I’m at a loss. Any help would be appreciated.

EVALUATE
VAR CaptionsCompanies =
        FILTER(
        GENERATE(
            GENERATE(
                SUMMARIZE( calendar, calendar[Month Offset] ),
                SUMMARIZE( captions, captions[Caption ID], captions[Sub Total] )
            ),
            SUMMARIZE( cost_centers, cost_centers[Reporting Company] )
        ),
        calendar[Month Offset] = 0 && captions[Caption ID] = 40 && captions[Sub Total] = 1 && cost_centers[Reporting Company] = 8
    )
VAR CaptionCompaniesAmounts =
    ADDCOLUMNS(
        SUMMARIZE(
            CaptionsCompanies,
            calendar[Month Offset],
            captions[Caption ID],
            captions[Sub Total],
            cost_centers[Reporting Company]
        ),
        "AvgNewBud",
            CALCULATE(
                DIVIDE(
                    DIVIDE( [Core Deductions from Revenue - Bud] * [Budget - OperRev/Core], [Source - OperRev/Core] ),
                    [Amounts - Budget Count]
                ),
                KEEPFILTERS( CaptionsCompanies )
            )
    )
RETURN
    CaptionCompaniesAmounts

Hi @kaDargo! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

Yes, I’m having trouble catching on that’s for sure. I clearly don’t understand and even worse, my questions aren’t making sense to folks.

I’m going to try one more time.

I’m attaching a new version of the pbix and modifying my question.

I built a dax query in TE3 (attached as “DAX Query 1.dax”), that works beautifully. It returns the number I’m expecting. When I replace EVALUATE statement with the measure name and the RETURN statement with the variable and no curly brackets, it returns blank (attached as “Unusual Items Filter Value.measure”)
AVERAGEX with virtual table.pbix (3.7 MB)
DAX Query 1.dax (2.5 KB)
Unusual Items Filter Value.measure (2.0 KB)

I don’t get it.

Hi there…we can’t download the Unusual items filter value.measure file as the extension (.measure) isn’t recognized by any software.
what is the expected results that you are looking for?

thanks
Keith

Unusual Items Filter Value.measure (2.0 KB)

Here it is as a txt file.

when i download that .measure we can’t download it because of the extension. rename as a .txr, or put the information into a word document with proper extension
no software will recognize the file it to be able to download it

Whoops, here it is again.
Unusual Items Filter Value.measure (2.0 KB)

For some reason, it won’t let me upload the file with a new extension, so I’m pasting the contents in here.

Unusual Item Filter Value = 
VAR selectedComparison = "Total Operating Revenue"
VAR selectedReportName = "260 Report Name"
VAR selectedMonthOffSet = 0
VAR selectedCaption = "Medical"
VAR selectedCompany =
    MINX( FILTER( 'structure', 'structure'[Report Name] = selectedReportName ), 'structure'[Reporting Company] )
VAR ActCompNum =
    CALCULATE(
        [MTD Source],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedComparison )
    )
VAR ActCompDen =
    CALCULATE(
        [MTD Source],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedCaption )
    )
VAR BudCompNum =
    CALCULATE(
        [MTD Budget],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedComparison )
    )
VAR BudCompDen =
    CALCULATE(
        [MTD Budget],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedCaption )
    )
VAR ActRatio = DIVIDE( ActCompNum, ActCompDen )
VAR BudRatio = DIVIDE( BudCompNum, BudCompDen )
VAR BudActRatio = DIVIDE( BudRatio, ActRatio )
   
VAR FV1 = 
    CALCULATETABLE(
        SELECTCOLUMNS(
            amounts,
            "CC", RELATED( cost_centers[Cost Center] ),
            "Acct",RELATED( chart_of_accounts[Account ID] ),
            "@WeightedAvgBud", [Amounts - Budget] * BudActRatio            
        ),
        'calendar'[Month Offset]=selectedMonthOffSet,
        cost_centers[Reporting Company]=selectedCompany,
        captions[Caption]=selectedCaption
    )
    
VAR FV2 = AVERAGEX(FV1,[@WeightedAvgBud])
RETURN
    FV2

what is your expected results that you are looking for?

I would like to understand why the measure doesn’t work while the dax query does.

i don’t know…but if i don’t see the expected results.

The query is returning 11338.60. The measure returns blank.

Is that what you’re asking?