Forum Discussion

sinanalmac's avatar
sinanalmac
Resolver I
3 years ago

Matrix Table SubTotal

Hi All, 

 

I have Two tables 

 

One's name v_PBI_CEScoreCard_CERegion and colums are; 

RepPointName, SalesRepName, PersonalCost

The other one  is Dim_RepPoint  and its colums are 
RepPointName, RepPointCost
I create  a relationship  between these two tables using by RepPointName Column. 


I want to create a mesure  for a maxrix visuals Row SubTotals . And I create This:

TotalCost With RepPoint = SUM(v_PBI_CEScoreCard_CERegion[PersonalCost]) + SUMX(VALUES(v_PBI_CEScoreCard_CERegion[RepPointName]), LOOKUPVALUE(Dim_RepPoint[RepPointCost], Dim_RepPoint[RepPointName], v_PBI_CEScoreCard_CERegion[RepPointName]))

This one is showed right calculation at the row subtotals.  but  it also add  RepPointCost    for every SalesRep by row . I just want to see RepPointCost  into to subtotal . Can you  helpme  about it?

Here  is  example about it

 

 

 

Total are  correct but   rows are not.   

Best Regards
Sinan 



Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    sinanalmac, Try a change like

     

    TotalCost With RepPoint = SUM(v_PBI_CEScoreCard_CERegion[PersonalCost]) + SUMX(v_PBI_CEScoreCard_CERegion[RepPointName], Maxx(Dim_RepPoint[RepPointCost], Dim_RepPoint[RepPointName], v_PBI_CEScoreCard_CERegion[RepPointName]))

    • sinanalmac's avatar
      sinanalmac
      Resolver I
      3 years ago

      Hello , 

       

      Thank you amitchandak . 

      Your code was made an erorr. I Couldn't provide the second argument

       

      I inspired your code and wrote this but it also wasn't worked 😞

      TotalCost With RepPoint = SUM(v_PBI_CEScoreCard_CERegion[PersonalCost]) + 
  CALCULATE(MAX(Dim_RepPoint[RepPointCost]), 
    FILTER(Dim_RepPoint, 
      Dim_RepPoint[RepPointName] IN VALUES(v_PBI_CEScoreCard_CERegion[RepPointName])
    )
  )


       regards

      Sinan