Valid measure for two fact tables

[jacinto]

Hi all,

I have two fact tables that couldn't be merged.  The following is dummy data and sample content. The Main table contain most of the data, however, I have also small table with extra information (202120) that I have to take into account to calculate total Emissision.  For 2022, it is easy, for each item just multiply volume by Average factor= totalvolume*average factor=3*(+.5+0.9)/2=2.1

For 2021 and 2020 and only fo wheat and soy we have origin, therefore instead of average factor we have to take specifc factor. Example for wheat, 4*0.5+1*0.9=2.9, basically CA and EC factor instead of average. 

The question is, is it possible to have a single measure based on the logic mentioned above? I would greatily appreciate if you guide me how I can achieve this. Thanks in advance!

[bolfri]

Yes, you can do that by adding dim_origin table that connects 202120 table with factor table, like you did with intermediate. Then it will understand which row corresponds to witch origin. 🙂

You can also share the excel with this sample data that you showed here, so I can show you the relationship and measures. 🙂

[jacinto]

It seems I don't have the priviledge to attach the excel but here is the copy paste. 

Main table

item	volume	value	year	othor columns
wheat	3	56	2022	x
barley	2	66	2022	x
soy	6	34	2022
vitamin	1	99	2022
amino	1	123	2022
wheat	4	11	2021
barley	3	44	2021
soy	1	67	2021
vitamin	4	42	2021
amino	1	3	2021
wheat	9	11	2020
barley	4	34	2020
soy	2	88	2020
vitamin	9	2	2020
amino	10	444	2020

202120 table

item	volume	year	origin
wheat	4	2021	CA
wheat	1	2021	EC
soy	4	2020	BR
soy	2	2020	CN

Factor table 

item	factor	origin
wheat	0.5	CA
barley	0.3	BR
soy	0.8	CN
vitamin	1	ET
amino	3	AR
wheat	0.9	EC
barley	0.1	GLO
soy	2	BR
vitamin	2	ET
amino	7	US

[bolfri · Accepted solution]

dim_item

dim_item = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[item]),
        GROUPBY(factor,factor[item]),
        GROUPBY(main,main[item])
    )
)

dim_origin

dim_origin = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[origin]),
        GROUPBY(factor,factor[origin])
    )
)

dim_year

dim_year = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[year]),
        GROUPBY(main,main[year])
    )
)

Relationship

Volume Sold = SUM('202120'[volume ])

Emmision =

SUMX(

    SUMMARIZE(

        factor,

        dim_item[item],

        factor[factor],

        dim_origin[origin],

        "VolumeForFactor",[Volume Sold]

    ),

    [VolumeForFactor]*factor[factor]

)

Result:

[jacinto]

Thank you very much, it was immensly helpful! Ultimately I am trying to come up with one measure that works in different context. I calculated EmissionM for main table like below and then another formula to combine Emission from 202120 table and maint able(Total Emission) 

EmisionM =

SUMX(

    SUMMARIZE(

        factor,

        dim_item[item],

       "avf",AVERAGE( factor[factor]),

        "VolumeForFactorM",[Volume SoldM]

    ),

    [VolumeForFactorM]*[avf])

Emission Total = var selectedyr = IF(SELECTEDVALUE(dim_year[year])==2020 || SELECTEDVALUE(dim_year[year])==2021, TRUE(),FALSE())

        var selectedcrop = IF(SELECTEDVALUE(dim_item[item])=="soy" || SELECTEDVALUE(dim_item[item])=="wheat",TRUE(),FALSE())

        var tot=IF(selectedyr && selectedcrop,[Emmision],[EmmisionM])

        return tot

It works even though the total is not correct and I believe there must be better way of making it work under any context. 

[jacinto]

Any suggestions?

[amitchandak]

@jacinto , if these measure are coming from two fact use common dimension to sum up

like

Sumx(Item , calculate( [totalvolume]*[average factor]) )

where totalvolume and average factor are measure from two facts

[jacinto]

But factor is only on the DM table not on the facts table. Aslo volume exisits in both Main table and 202120 table for 2021 and 2021 (basically 202120 table is subset of Main table with origin value known, therefore volume should be multiplied with factor based on the origin). I hope I managed to explain.