Forum Discussion
Solved
Valid measure for two fact tables
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 infor...
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:
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
Solution Sage
Solution Sagedim_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:
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 totIt works even though the total is not correct and I believe there must be better way of making it work under any context.Any suggestions?