Incorrect sum and average, display count and average

[rbalza]

Hi PowerBi Superstars,

Hope you have a good midweek. I have table displaying TopN with its company name and type of jobs.

On the topN, it is displaying the incorrect sum and the rest of the sum of topN were blanks.

Also, when I try to display the count of each jobs and its average, it disregards the topN value. See attached sample file and the measures that I have used. https://www.dropbox.com/s/xszrjtxt9oo8k76/sampledata.pbix?dl=0

Thank you for this wonderful community. Hope I'll contribute as well as I progress.

Measure for Counting jobs

# of Jobs by Group Owner = SUMX(
    SUMMARIZE(FactFinalisedInvoicesDetailed, FactFinalisedInvoicesDetailed[Invoice Type],
        "Jobs", [Rank by Invoice Type] ),
        [Jobs] )

Displaying average of jobs

Average Total Invoice By Invoice Type = AVERAGEX( VALUES(FactFinalisedInvoicesDetailed[Invoice Type]), [Total Invoice] )

RankX measure

Rank by Invoice Type = 
RANKX(
    ALL(GroupedFinalisedInvoices[Group Owner]),
        CALCULATE( [Total Invoice],
            ALLSELECTED(FactFinalisedInvoicesDetailed[Invoice Type] ) ), , DESC )

topN measure

Total Invoice By Rank Group = 
SUMX(
    KEEPFILTERS(
        FILTER(
            ALLSELECTED( FactFinalisedInvoicesDetailed[Invoice Type] ), 
            COUNTROWS(
                FILTER(
                    'Customer Rank',
                    [Rank by Invoice Type] >= 'Customer Rank'[Min Rank]
                    && [Rank by Invoice Type] <= 'Customer Rank'[Max Rank] )
            ) > 0
        )
    ), [Total Invoice]
)

Displaying count of jobs - where is the next topN?

Displaying average of jobs - incorrect average and the next topN were missing

[AlexisOlson]

Let's try starting from scratch.

Define [Total Invoice] as SUM ( FactFinalisedInvoicesDetailed[Invoice Total] ) and modify the ranking measure to return blanks if we're in the wrong ranking group.

Rank by Group Owner =
VAR MinRank = SELECTEDVALUE ( 'Customer Rank'[Min Rank] )
VAR MaxRank = SELECTEDVALUE ( 'Customer Rank'[Max Rank] )
VAR GroupRank =
    RANKX (
        ALL ( Customer[Group Owner] ),
        CALCULATE (
            [Total Invoice],
            ALLSELECTED ( FactFinalisedInvoicesDetailed[Invoice Type] )
        ),
        ,
        DESC
    )
RETURN
    IF (
        ISINSCOPE ( 'Customer Rank'[Customer Group] ),
        IF ( GroupRank >= MinRank && GroupRank <= MaxRank, GroupRank ),
        GroupRank
    )

This makes calculating the total easier.

Total Invoice by Rank Group =
SUMX (
    FILTER (
        VALUES ( Customer[Group Owner] ),
        NOT ISBLANK ( [Rank by Group Owner] )
    ),
    [Total Invoice]
)

Note that I'm using Customer[Group Owner] in the measures and visual instead of the column from GroupFinalisedInvoices. This is for simplicity to avoid having to think about the bi-directionally filtering many-to-many relationship.

[AlexisOlson · Accepted solution]

Screenshot: