Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Measure does not total

Jump to Best Answer
  • 1.  Measure does not total

    Posted Jul 24, 2018 11:53 AM
    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.
    Data displays but without a total MRC

    I have verified the MRC column is a number in my table.
    MRC is a number
    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!













































    ------------------------------
    Barbara Forsman
    MIS
    NebraskaLink
    Lincoln NE
    4028171425
    ------------------------------


  • 2.  RE: Measure does not total

    Top Contributor
    Posted Jul 24, 2018 04:31 PM
    Edited by William Rodriguez Jul 24, 2018 04:32 PM
    Greetings @Barbara Forsman:

    Great question!

    The majority of the question resolves deeper than just the question - we really need to see how your data is structured.
    From what I can tell, the following is happening:

    The issue is in SelectedValue. SelectedValue is 'syntax sugar' for 'IF( HASONEVALUE() , VALUES())'
    Translation:
    • If I (the context evaluation) have one value, then return that value
    • Else, return blank

    What is happening is that in your matrix, the 'evaluation context' is on the row level. So the equation is saying:
    • For 'Quote Number' X, find the respective MRC number filtered by the the lowest BW & Max Term (this is incorporating your variables)
    • On the Grand total field, there are multiple MRC numbers, so return blank. 

    You can correct this by doing something like the following:

    VAR A
    VAR B
    RETURN
        IF (
            Countrows (Table) = Countrows (ALL ( Table) ) , /*This check the granularity - if true, then all fields are shown*/
            SUMX ( Equation Over Table )
            Equation
        )

    Something along those lines should help - if you can upload a 'dummy' data table, that would be great to give a more exact equation / resolution.

    Hope this helps!
    William

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------



  • 3.  RE: Measure does not total

    Posted Jul 24, 2018 04:57 PM
    Thank you!  I will see if I can apply your logic to my data.
    In the meantime I've uploaded scrubbed data file here
    I appreciate your help!!

    ------------------------------
    Barbara Forsman
    MIS
    NebraskaLink
    Lincoln NE
    4028171425
    ------------------------------



  • 4.  RE: Measure does not total

    Top Contributor
    Posted Jul 24, 2018 08:50 PM
    Edited by William Rodriguez Jul 24, 2018 08:58 PM
      |   view attached
    Greetings @Barbara Forsman:

    Once again, great post - great example of how to measure granularity within a measure!

    Below are my:
    1. Final Results
    2. A few Caviots
    3. Final Equation

    1) Final Results:
    You can see that the granularity between the 'OldEquation' and the 'NewEquation' are consistent*, albeit at the grand total level where the New equation sums all of the above metrics (image one below).

    *A few minor items will be covered in section two

     IMAGE ONE
    2) Caviots

    There are a few Quote numbers that have the same lowest BW and max Term (See image 2.1 below - quote numbers on left)
    Image 2.2 details one example - Quote Number "QUO-02226-Y2S3S9" has two MRC codes for the lowest BW & MAX Term.

    What this means is that my new equation will add these values together and add them to the report. The old report would not show these quote number MRC values at all (reference image 2.1). A business decision needs to be made as to include these values or not

    IMAGE 2.1

    IMAGE 2.2


    3) FINAL EQUATION
    With all these factors in mind, I am ready to present the final equation. The PBI file has been attached for your reference.

    NewEquation =
    VAR MinBW =
        MIN ( Table1[BW] )
    VAR MaxT =
        MAX ( Table1[Term] )
    VAR StandardEquation =
        CALCULATE ( SUM ( Table1[MRC] ), Table1[BW] = MinBW, Table1[Term] = MaxT )
    VAR Total_Equation =
        VAR Final_Table =
            GENERATE (
                Table1,
                ROW (
                    "EQUATION",
                    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
                )
            )
        RETURN
            CALCULATE ( SUMX ( Final_Table, [EQUATION] ) )
    VAR YEAR =
        SELECTEDVALUE ( 'Calendar'[Year] )
    VAR TOTAL_TYPE =
        COUNTROWS ( VALUES ( 'Calendar'[Year] ) )
    VAR Total_Indicator =
        CALCULATE (
            COUNTROWS ( Table1 ),
            'Calendar'[Year] = YEAR,
            ALLSELECTED ( Table1 )
        )
    RETURN
        IF (
            COUNTROWS ( Table1 ) = Total_Indicator
                || ( TOTAL_TYPE > 1 ),
            Total_Equation,
            StandardEquation
        )




    --END OF FORMULA

    Thanks!
    William

    #DAX #PowerBI #Granularity #Variables​​

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------

    Attachment(s)

    pbix
    Barbara Help.pbix   219 KB 1 version


  • 5.  RE: Measure does not total

    Posted Jul 25, 2018 09:01 AM
    Wow - no wonder I wasn't able to do this on my own!
    We would want to use only one MRC for the duplicates.  Those are anomaly quotes that were entered incorrectly.  How would that alter the equation?

    Thank you so much!!

    ------------------------------
    Barbara Forsman
    MIS
    NebraskaLink
    Lincoln NE
    4028171425
    ------------------------------



  • 6.  RE: Measure does not total
    Best Answer

    Top Contributor
    Posted Jul 25, 2018 09:33 AM
      |   view attached
    Greetings @Barbara Forsman:

    ​I'm glad you enjoy! At the end, I am uploading a new final equation that is better from a performance perspective and easier to read (hopefully the logic flow will become more apparent!).

    In regards to your questions / comments:
    • Question: "We... want to use only one MRC for the duplicates"
      • My best trial of an answer: Image 2.2 shows what the data looks like. For Quote Number "QUO-02226..." there are two MRC values that have a lowest BW of 20 and highest term of 60. The question is which value do you want to use (i.e., MRC value 3679 or 899)? This answer will determine how to scope the equation.
    • Comment: "Those are anomaly quotes..."
      • My thoughts: Understood - so now the question is how do we identify the anomaly quotes programmatically so that the equation removes the undesired quote (this ties with Question one above).
    • Question: "How would that alter the equation?"
      • My best attempt answer: It depends. If there is a logically condition like: 'in the case of a tie, pick the smallest number' then we would need to Modify both the StandardEquation and TotalEquation to reflect the business needs (reference equation below).
        • Example (modifying the TotalEquation variable): add a condition VAR THREE where you select the smallest available MRC value and then add that variable to the VAR EQUATION as a condition of the table filter (something like VAR EQUATION  = ... Table1[BW] = ONE && Table1[TERM] = TWO && Table1[MRC]=THREE).
    I know this is a lot - but I hope this helps!

    NEW FINAL EQUATION =
    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
        )

    --END OF EQUATION

    Thanks!
    William

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------

    Attachment(s)

    pbix
    Barbara Help.New.pbix   222 KB 1 version


  • 7.  RE: Measure does not total

    Posted Jul 25, 2018 10:16 AM
    Thank you again, William!  This is very helpful. Can you indulge me with one more question?
    I tried using this in a matrix to show the MRC by month - it totals but doesn't show the total by month. The relationship looks good between createdon and the Calendar.  I'm confused why this doesn't work?
    Month totals do not display

    ------------------------------
    Barbara Forsman
    MIS
    NebraskaLink
    Lincoln NE
    4028171425
    ------------------------------



  • 8.  RE: Measure does not total

    Posted Jul 25, 2018 10:56 AM
    I see it - I changed from Calendar [Year] in the equation to use month instead and it works perfectly.
    You deserve a medal for helping me with this - thank you so much!!

    ------------------------------
    Barbara Forsman
    MIS
    NebraskaLink
    Lincoln NE
    4028171425
    ------------------------------



  • 9.  RE: Measure does not total

    Top Contributor
    Posted Jul 25, 2018 11:55 AM
    Edited by William Rodriguez Jul 25, 2018 11:55 AM
    Greetings @Barbara Forsman:

    Glad everything worked out!

    I may not be able to get a medal, but marking a solution / comment as the 'Best Answer' does help, and would be greatly appreciated!

    Until then, hope all goes well.

    Thanks!
    William​

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------



  • 10.  RE: Measure does not total

    Posted May 28, 2021 10:06 AM

    Hi William, thank you for your post, it is really helpful. I have a similar situation and got stacked, if you'll have few minutes to have a look, I'll be very thankful. Basically, I have a model with some products, shops and targets in a matrix, for most of the cases the DAX measure works, but in some of them, it gives me headaches. The problem I have is with the total (SUM TO FIX) when there are 2 different targets within same shop (Shop 2), in this case, expected result should be: 2693,12.

    Do you have any ideas how this could be fixed?

    Please find attached the .pbix file and the expected result.

    Thank you very much in advance.



    ------------------------------
    Sturza Roman
    Power BI
    ------------------------------

    Attachment(s)

    pbix
    TEST.pbix   31 KB 1 version