Forum Discussion

Islam's avatar
Islam
Icon for Helper V rankHelper V
5 years ago
Solved

A problem with martix total

hey i got i matrix report in power bi project and i got some some measures but one of them doesn't display total in marrix and i dun know why like in the attached photo in totalpayback vlaues ...
Need Help
  • nandic's avatar
    nandic
    5 years ago

    Islam , as mention at the begining, the reason for this os because in total row the difference is in minus and there is no result. The logic is as on detail row, if openings<expense, return 0.
    So in order to have "sum" in totals, you need to use IfHasOneValue.
    Attached file.

    Example:
    TotalPayback New =
    var _filterArea = FILTER('Table','Table'[Openings]>'Table'[Expenses])
    RETURN
    IF(HASONEVALUE('Table'[YearMonth]),
    IF([TotalOpenings]-[TotalExpenses] >0,[TotalOpenings]-[TotalExpenses]),
    SUMX(_filterArea,'Table'[Openings]-'Table'[Expenses])
    )
     
    Regards,
    Nemanja Andic

    Attached file.
Islam's avatar
Islam
Icon for Helper V rankHelper V
5 years ago
RequiredToPay = IF([TotalExpenses]-[TotalOpenings] >0,[TotalExpenses]-[TotalOpenings])
this measure is working fine but this one
TotalPayback = IF([TotalOpenings]-[TotalExpenses] >0,[TotalOpenings]-[TotalExpenses])
give me the values without totals
that's the problem and i don't know what is the problem here
nandic's avatar
nandic
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Islam , could you please add additional information:
1) screenshot where we can also see results for measure "TotalExpenses"
2) dax for measures "TotalExpenses" and "TotalOpenings"
3) screenshot of model (relationships between tables)
All this is needed to find out where is the issue.

Thanks,
Nemanja

  • Islam's avatar
    Islam
    Icon for Helper V rankHelper V
    5 years ago

    Sure here all info u need

     

      

     

      • nandic's avatar
        nandic
        Icon for Resident Rockstar rankResident Rockstar
        5 years ago

        Islam , as mention at the begining, the reason for this os because in total row the difference is in minus and there is no result. The logic is as on detail row, if openings<expense, return 0.
        So in order to have "sum" in totals, you need to use IfHasOneValue.
        Attached file.

        Example:
        TotalPayback New =
        var _filterArea = FILTER('Table','Table'[Openings]>'Table'[Expenses])
        RETURN
        IF(HASONEVALUE('Table'[YearMonth]),
        IF([TotalOpenings]-[TotalExpenses] >0,[TotalOpenings]-[TotalExpenses]),
        SUMX(_filterArea,'Table'[Openings]-'Table'[Expenses])
        )
         
        Regards,
        Nemanja Andic

        Attached file.
  • Islam's avatar
    Islam
    Icon for Helper V rankHelper V
    5 years ago

    weird I found that the total of those two measures 

    requiredtopay and totalpayback is subtracted from each others and total is the net value