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

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

      Really awesome sir but if u take a closer look at the requiredtopay column total it calculated by subtracting totalpayback total sum from requiredtopay total sum 

      40812628

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

      Anyway I'll check if i can do it myself and if so I'll let you know

      Really appreciate it sir

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

      excuse me sir can i send u the pbix file ?

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

        Yes, you can send the file and i will take i look.