Forum Discussion

Venkatesh_'s avatar
Venkatesh_
Icon for Helper I rankHelper I
2 years ago
Solved

Need help in dax while using matrix visual - Row total is not giving me the correct result

Can anyone of you help me to figure out why my subtotals are not populating here please? If i add the all selected to the product level still it end up giving me the wrong result in matrix visual. ...
Need Help
Show and Tell
  • Venkatesh_'s avatar
    Venkatesh_
    2 years ago

    Anonymous   Thanks for your reply. Still, your measure is missing overall total row value. Here is the correct measure which helps me to acheieve desired result

    Proj. Annual Var =
    VAR PYI1 = [Predicted Year Income 1]
    VAR PYI2 = [Predicted Year Income 2]

    VAR Result =
        IF(
            ISINSCOPE(Sites[Name]) && ISINSCOPE(BITransactions[ProductName]),
            PYI1 - PYI2,
            SUMX(
                SUMMARIZE(
                    BITransactions,
                    Sites[Name],
                    BITransactions[ProductName],
                    "Diff", [Predicted Year Income 1] - [Predicted Year Income 2]
                ),
                [Diff]
            )
        )

    RETURN Result

      

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Venkatesh_ ,

 

You can try this.

Measure 4 = 
var _table1=
DISTINCT('BITransactions'[ProductName])
var _table2=
DISTINCT('Sites'[Name])
var _table3=
CROSSJOIN(
    _table1,_table2)
var _table4=
ADDCOLUMNS(
    _table3,"1",[Predicted Year Income 1],
    "2",[Predicted Year Income 2])
var _table5=
ADDCOLUMNS(
    _table4,"3",[1] - [2])
var _value=
SUMX(
    FILTER(
        _table5,[ProductName]=MAX('BITransactions'[ProductName])),[3])
return
IF(
    HASONEVALUE(
        'Sites'[Name]),[Proj. Annual Var],
    IF(
        HASONEVALUE(BITransactions[ProductName]),_value))

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Venkatesh_'s avatar
Venkatesh_
Icon for Helper I rankHelper I
2 years ago

Anonymous   Thanks for your reply. Still, your measure is missing overall total row value. Here is the correct measure which helps me to acheieve desired result

Proj. Annual Var =
VAR PYI1 = [Predicted Year Income 1]
VAR PYI2 = [Predicted Year Income 2]

VAR Result =
    IF(
        ISINSCOPE(Sites[Name]) && ISINSCOPE(BITransactions[ProductName]),
        PYI1 - PYI2,
        SUMX(
            SUMMARIZE(
                BITransactions,
                Sites[Name],
                BITransactions[ProductName],
                "Diff", [Predicted Year Income 1] - [Predicted Year Income 2]
            ),
            [Diff]
        )
    )

RETURN Result