Forum Discussion
how to summarize one table whose data are from two fact tables
- 4 years ago
Ok, see if this works.
Count Items w/forecast = COUNT ( 'Forecast Fact'[Item] )Above Forecast = COUNTROWS ( SUMMARIZE ( FILTER ( 'SKU List', [Production] >= [Sum Forecast] && NOT ( ISBLANK ( [Production] ) ) ), 'SKU List'[Item], 'Vendor Lookup'[Vendor] ) )I've attached the PBIX file
PaulDBrown Hey Paul, thanks for your inputs. I might not say myself clearly. I actually know we can use measure to create the current table as below or as you mentioned above. But that might ignore some vendors whose total level difference between forecast and production is 0, but in each item, those vendors has big variance. Given that, I want to measure how many items hit the forecast per each vendor. So except for checking the total difference in between forecast and production, I want to check the item level hit rate based on the selected month and selected forecast version. that's why I thought I should use summarizecolumn to combine item, vendor and month into one new table, and then I can have the item associated vedor level difference between forecast and production. but it seems summarizecolumn doesn't work for me. That's my issue. Do you or someone elese how to solve that one? Thanks!
Ok, see if this works.
Count Items w/forecast =
COUNT ( 'Forecast Fact'[Item] )
Above Forecast =
COUNTROWS (
SUMMARIZE (
FILTER (
'SKU List',
[Production] >= [Sum Forecast]
&& NOT ( ISBLANK ( [Production] ) )
),
'SKU List'[Item],
'Vendor Lookup'[Vendor]
)
)
I've attached the PBIX file