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.

22 Replies

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

    Hi Islam ,
    Total value in matrix doesn't behave as we used to see it in Excel.
    Total value is not sum of all values in that column. On the other hand, total value is calculated for that "specific total row".
    Example: if you are comparing if RequiredToPay is not blank then return some value, it will work on "detail row". 
    But for "total row" there is a value so it will return some value. 
    Most popular solution to this is to create condition to check if it is a detail row or total row.
    Here we use IfHasOneValue or IsFiltereFunction.
    More info you can find here: https://powerpivotpro.com/2014/01/grand-total-mania-totals-at-top-multiple-totals/ 

    Regards,
    Nemanja Andic

    • 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

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    It would be better to give us a sample file where we can actually see the code behind this measure. 
    Then we can help you change this measure

    • 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
  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Islam In powerbi, there are many relationships between multiple tables which determines the output. So it is possible that there might be some discrepency in the relationship between tables which resulting in mismatch in the output. I would suggest you to check the relationship between date table and other table which sometime is main cause of error or issue. 

     

    Let me know if you need further assistance on this. You may try sharing your powerbi file or some sample data to test it at our end. 

    • 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
  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    With your first picture (showing Total Expenses column)
    You can see that 
    Total Openings - Total Expenses < 0
    So Total Payback si not showing because you explicitly 
    request Total Openigs - Total Expenses to be greather than 0 

    TotalPayback = IF([TotalOpenings]-[TotalExpenses] >0,[TotalOpenings]-[TotalExpenses])

     

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

      so how should i modify or alter this measure to get the total of column

       

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    I assume you'll want the grand total to be 
    "The sum of all individual Total Payback value"

    In this case, Let's say you have account name on the rows, you can iterate on each of them,
    calculating Total Paypack, then add everything at the end. 
    Something like this :

    Total_Payback_Matrix = 
SUMX(
	VALUES( Account[Account Name] ),
	[Total Payback]
)