Forum Discussion

jeeva_chandru's avatar
jeeva_chandru
New Member
10 months ago
Solved

Total Issue in Matrix Visual in Power BI

Hi All,
 
I am looking for some assistance with a DAX measure in Power BI. I have a dashboard where I'm using 'Vertical' in the rows and 'Region' in the columns, and the measure is M-Sufficency_Amount($M).
The issue is that the row and column totals are not calculating correctly and are showing a different value than the simple sum of the individual rows. I suspect this is a filter context issue within my DAX measure, but I'm not sure how to resolve it. 
 
Any guidance on how to fix this would be greatly appreciated. Thank you in advance!

 

 

 

 

 

DAX Caluclations:

 

M-Sufficency_Amount($M) =
VAR SelectedMethod = SELECTEDVALUE('ForecastMethodTable'[ForecastMethod])
RETURN
SWITCH(
    SelectedMethod,
    "CONVERSION RATE", [M-Conversion_Rate],
    "PROBABILITY METHOD", [M-Probability_Method],
    "CTF", [M-CTF],    
    [M-Conversion_Rate]  // Handle case where no valid method is selected
)
 
M-Conversion_Rate =
[M-CompletedSales_Debug] +
(
    [VAR M-OtherSales_Debug] *
    LOOKUPVALUE(
        SummarizedTable[Average Conversion Rate],
        SummarizedTable[Vertical], MAX(AOP_EDW_MERGED[AOP All Combinations.VERTICAL]),
        SummarizedTable[Region], MAX(AOP_EDW_MERGED[Region])
    )
)
 
M-CompletedSales_Debug =
VAR CurrentYear = YEAR(TODAY())
RETURN
    SUMX(
        FILTER(
            AOP_EDW_MERGED,
            YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
            AOP_EDW_MERGED[SALES STAGE ID] = 9
        ),
        AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    ) / 1000000  // Dividing by 1,000,000 for consistency with the main measure
 
VAR M-OtherSales_Debug =
VAR CurrentYear = YEAR(TODAY())
RETURN
    SUMX(
        FILTER(
            AOP_EDW_MERGED,
            YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
             NOT AOP_EDW_MERGED[SALES STAGE ID] IN {7, 8, 9}
        ),
        AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    ) / 1000000  // Dividing by 1,000,000 for consistency with the main measure
 
SummarizedTable =
SUMMARIZE(
    FILTER(
        'Conversion Rate',
        NOT(ISBLANK('Conversion Rate'[Vertical]))
    ),
    'Conversion Rate'[Vertical],  
    'Conversion Rate'[Region],
    "Average Conversion Rate", AVERAGE('Conversion Rate'[CONVERSIONRATE])
)
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    10 months ago

    Hi jeeva_chandru,

     

    The row/column totals in your M-Sufficency_Amount($M) matrix are wrong because the DAX measures inside (M-Conversion_Rate, M-CompletedSales_Debug, etc.) are row-context dependent (they rely on MAX() and LOOKUPVALUE()), and totals don’t have the same filter context as each cell.

     

    Try below DAX

     

    M-Conversion_Rate =
    VAR CurrentYear = YEAR(TODAY())
    RETURN
    SUMX(
    VALUES(AOP_EDW_MERGED[Vertical]),
    VAR _Vertical = AOP_EDW_MERGED[Vertical]
    RETURN
    SUMX(
    VALUES(AOP_EDW_MERGED[Region]),
    VAR _Region = AOP_EDW_MERGED[Region]
    VAR _CompletedSales =
    CALCULATE(
    SUMX(
    FILTER(
    AOP_EDW_MERGED,
    YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
    AOP_EDW_MERGED[SALES STAGE ID] = 9
    ),
    AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    )
    ) / 1000000
    VAR _OtherSales =
    CALCULATE(
    SUMX(
    FILTER(
    AOP_EDW_MERGED,
    YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
    NOT AOP_EDW_MERGED[SALES STAGE ID] IN {7,8,9}
    ),
    AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    )
    ) / 1000000
    VAR _AvgConvRate =
    CALCULATE(
    AVERAGE('Conversion Rate'[CONVERSIONRATE]),
    'Conversion Rate'[Vertical] = _Vertical,
    'Conversion Rate'[Region] = _Region
    )
    RETURN
    _CompletedSales + (_OtherSales * _AvgConvRate)
    )
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

2 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    Hi jeeva_chandru,

     

    The row/column totals in your M-Sufficency_Amount($M) matrix are wrong because the DAX measures inside (M-Conversion_Rate, M-CompletedSales_Debug, etc.) are row-context dependent (they rely on MAX() and LOOKUPVALUE()), and totals don’t have the same filter context as each cell.

     

    Try below DAX

     

    M-Conversion_Rate =
    VAR CurrentYear = YEAR(TODAY())
    RETURN
    SUMX(
    VALUES(AOP_EDW_MERGED[Vertical]),
    VAR _Vertical = AOP_EDW_MERGED[Vertical]
    RETURN
    SUMX(
    VALUES(AOP_EDW_MERGED[Region]),
    VAR _Region = AOP_EDW_MERGED[Region]
    VAR _CompletedSales =
    CALCULATE(
    SUMX(
    FILTER(
    AOP_EDW_MERGED,
    YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
    AOP_EDW_MERGED[SALES STAGE ID] = 9
    ),
    AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    )
    ) / 1000000
    VAR _OtherSales =
    CALCULATE(
    SUMX(
    FILTER(
    AOP_EDW_MERGED,
    YEAR(AOP_EDW_MERGED[EDW_Pipeline.COMPLETION DATE]) = CurrentYear &&
    NOT AOP_EDW_MERGED[SALES STAGE ID] IN {7,8,9}
    ),
    AOP_EDW_MERGED[EDW_Pipeline.Est. Sell Price]
    )
    ) / 1000000
    VAR _AvgConvRate =
    CALCULATE(
    AVERAGE('Conversion Rate'[CONVERSIONRATE]),
    'Conversion Rate'[Vertical] = _Vertical,
    'Conversion Rate'[Region] = _Region
    )
    RETURN
    _CompletedSales + (_OtherSales * _AvgConvRate)
    )
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!