Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pedropbribeiro
New Member

Creating matrix from two tables and a calculation

Hello,

 

I'm trying to do a matrix with 2 tables, but it doesnt do the calculation properly.

 

This are my 2 tables: 

pedropbribeiro_0-1683131005029.png

Where JIRA.Intervention Area = LocationsCoordinates.Location

 

On the matrix, the total cost is incorrect (single values and totals):

pedropbribeiro_3-1683133501083.png

 

 

(JIRA.Resolved; LocationsCoordinates.Location; LocationsCoordinates.TotalCost)

 


Here we can see that, for example, on 14-04-2023 there is 7 rows in "Portugal - Lisboa":

pedropbribeiro_1-1683131386931.png

The correct calculation for this ex should be 7 * 175,06€.

 

Also the totals are incorrect too.

 

Thank you for your help.

1 ACCEPTED SOLUTION
MarkLaf
Solution Sage
Solution Sage

There is only one row in 'Locations Coordinates' for an associated TotalCost even as your filter context may have multiple rows from 'JIRA'.

To force PBI to sum 'Locations Coordinates'[TotalCost] for each row of JIRA, you'll need an iterator:

Measure = 
SUMX(
  JIRA,
  RELATED( 'Locations Coordinates'[TotalCost] )
)

 

View solution in original post

5 REPLIES 5
pedropbribeiro
New Member

Thank you for your reply. I will try to explain my case better. In JIRA table I have the date and the location that was made by someone. In the another table (LocationsCoordinate), I have every location with the costs. So, in JIRA will appear the same locatiom multiple times, but in LocationsCoordinate only once with the costs. I'm trying to, for every day that's in JIRA, calculate the costs that's in LocationsCoordinate. (I want the fisrt matrix. The second is just to demonstrate that was 7 dislocation to "Lisboa" for example (COUNT). The correct value should be 7* cost).

@pedropbribeiro 

If I correctly understand then @MarkLaf 's proposed solution should provide you with the result that you're looking for. 

MarkLaf
Solution Sage
Solution Sage

There is only one row in 'Locations Coordinates' for an associated TotalCost even as your filter context may have multiple rows from 'JIRA'.

To force PBI to sum 'Locations Coordinates'[TotalCost] for each row of JIRA, you'll need an iterator:

Measure = 
SUMX(
  JIRA,
  RELATED( 'Locations Coordinates'[TotalCost] )
)

 

Thank you. This work for me.

tamerj1
Super User
Super User

Hi @pedropbribeiro 

strange that the total cost column is in the LocationCoordinates table which is a dimension table! Are you sure about that? If so then how the ResolvedDate (which is in the JIRA fact table) is related to the total cost? The location is primary key (unique) in the LocationsCoordinates table therefore I guess in this case each location has a single total value so what does mean to split it over months? Also it is not clear what is the difference between the first and the second matrices?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors