cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon Member
Member

Measure of sum values from another table with relation of id and year

Hi,

 

I have a problem in the creation of measures from different tables and mixer them in the visual. As an example i have 2 tables (actuals and references) that both only can have a relation many to many (id).

 

  • Table actualsM1.JPG

     

  • Table ref
    M2.JPG

So, as you can see the table of actuals is in daly values and the ref in yearly values and related to the sames id

 

I want to create a measure in the table Actuals (because is where i have the majority of the measures and apart of that i want to create another measures using this new measure of ref annual value)

 

I know that I can use calculate to create a column in my actual table that takes the values from ref taking into account the id and year from both tables and then another measure from that new column in actuals. But i'm asking for a direct measure in actuals takt take values from the ref table.

Can someone help me on this?

 

Thanks in advance, regards!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Measure of sum values from another table with relation of id and year

try something like this - there is a Calendar table as well that has a Year column with join on date with Actuals table

Measure =
CALCULATE (
    SUM ( 'Table ref'[ref_annual] ),
    TREATAS (
        SUMMARIZE ( 'Table actuals', 'Table actuals'[id], 'Calendar'[Year] ),
        'Table ref'[id],
        'Table ref'[year]
    )
)

Capture.PNG

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Stachu Super Contributor
Super Contributor

Re: Measure of sum values from another table with relation of id and year

I'm not clear on what you're trying to achieve exaclty (what you describe seems more like a calculated column, measures can be located in any table and will have exactly same result)
regardless, I'm guessing you will need to use TREATAS
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

dobregon Member
Member

Re: Measure of sum values from another table with relation of id and year

Thanks @Stachu for the response

 

I want to have a visual like this (picture below) using measures created in the table actuals

 

idyearactualref
120181973780000
22018593429000

 

So i have summarized the actuals only by year using a measure sum(actuals) and then another column with the measure of ref that take the correct ref by id and year.

I have tried to use your link creating a measure like

Ref value= 
CALCULATE(
    SUM(Ref[ref_annual]),
    TREATAS(
        SUMMARIZE(Ref,Ref[id],Ref[Year]),
        actual[id],
        actual[Year]
    )

and it is not working

Stachu Super Contributor
Super Contributor

Re: Measure of sum values from another table with relation of id and year

try something like this - there is a Calendar table as well that has a Year column with join on date with Actuals table

Measure =
CALCULATE (
    SUM ( 'Table ref'[ref_annual] ),
    TREATAS (
        SUMMARIZE ( 'Table actuals', 'Table actuals'[id], 'Calendar'[Year] ),
        'Table ref'[id],
        'Table ref'[year]
    )
)

Capture.PNG

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
dobregon Member
Member

Re: Measure of sum values from another table with relation of id and year

yes, exactly i have tried this one minute ago and it works! thanks a lot. This is the solution!

 

Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 36 members 898 guests
Please welcome our newest community members: