Forum Discussion

barbforsman's avatar
barbforsman
Resolver I
8 years ago
Solved

Measure does not total

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 minB...
Need Help
  • barbforsman's avatar
    barbforsman
    8 years ago

    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
        )

     

barbforsman's avatar
barbforsman
Resolver I
8 years ago

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!

 

 

 

  • barbforsman's avatar
    barbforsman
    Resolver I
    8 years ago

    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
        )