Measure does not total

[barbforsman]

I have a measure that finds the smallest bandwidth and longest term for a quote and displays the MRC.  But the MRC does not total in a matrix.

This is the measure I'm using:
MinBWMaxT =
var minBWValue = min(BWT_MRC[BW])
var maxTermValue = max(BWT_MRC[Term])
return CALCULATE(SELECTEDVALUE(BWT_MRC[MRC]), BWT_MRC[BW] = minBWValue, BWT_MRC[Term] = maxTermValue)

My matrix displays the data correctly, but without the total.


I have verified the MRC column is a number in my table.

Do I need to alter my measure, or somehow create a new table based on the measure and then total the MRC?

Any help will be much appreciated!

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

That being said, I'm wondering if the issue is your SELECTEDVALUE because in the context of ALL this would return BLANK?

[barbforsman]

Forgive me, but can you help me apply this solution to my data?  Would it be something like this?

Measure2 = If(Hasonefilter(BWT_MRC[Qte-BwT]),
     If(BWT_MRC[MRC]<>[MinBWMaxT],0,SUM(BWT_MRC[MRC])),
           0)

This doesn't work because a single value for the column "MRC" in table "BWT_MRC" cannot be determined.

 I am relatively new to Power BI, but I am not using any slicer on the vis - my measure is looking at the table and finding the record with the lowest BW and longest Term for a quote.  I need to total these values found by the measure.  I'm just not sure how to use HasOneFilter to do this?

I've attached a scrubbed file with sample data for the columns as well as the Measure.

Thanks in advance for your help!

[v-chuncz-msft]

@barbforsman,

You may try a similar way as shown here.

[barbforsman · Accepted solution]

William Rodriguez gave me this answer that works:

VAR StandardEquation =
    VAR MinBW = MIN( Table1[BW] )
    VAR MaxT = MAX( Table1[Term] )
    RETURN
        CALCULATE(
            SUM( Table1[MRC] ) ,
            Table1[BW] = MinBW ,
            Table1[Term] = MaxT
        )
VAR Total_Equation =
    SUMX(
        Table1 ,
            VAR ONE =
                CALCULATE(
                    MIN ( Table1[BW] ) ,
                    FILTER(
                        Table1 ,
                        Table1[Quote Number] = EARLIER( Table1[Quote Number] )
                    )
                )
            VAR TWO =
                CALCULATE(
                    MAX ( Table1[Term] ) ,
                    FILTER(
                        Table1 ,
                        Table1[Quote Number] = EARLIER( Table1[Quote Number] )
                    )
                )
            VAR EQUATION =
                IF (
                    Table1[BW] = ONE && Table1[TERM] = TWO ,
                    Table1[MRC]
                )
            RETURN EQUATION
    )
VAR GRANULARITY_CHECK =
    VAR YEAR = SELECTEDVALUE( 'Calendar'[Year] )
    VAR TOTAL_TYPE = COUNTROWS( VALUES( 'Calendar'[Year] ) )
    VAR Total_Indicator =
        CALCULATE(
            COUNTROWS( Table1 ) ,
            'Calendar'[Year] = YEAR ,
            ALLSELECTED(Table1 )
        )
    RETURN
        COUNTROWS( Table1 ) = Total_Indicator || ( TOTAL_TYPE > 1 )
RETURN
    IF(
        GRANULARITY_CHECK = TRUE() ,
        Total_Equation ,
        StandardEquation
    )