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 reply. That's what i thought. So i planned to use summarizecolumn to create a new table including the data (SKU, Vendor, Month). But I think it doesn't work for me. Do you have any thoughts how I should do to have a dimension table? Thanks a lot! (You can see the attached pbix file)
The way you had the model setup was correct. Now you use the fields from the dimension tables for the visuals, slicers, filters and measures (the tables within the blue line).
These table fields provide the filter context for both fact tables, so you can set up the table visual with the fields for vendor and month from the corresponding dimension tables to bring in the relevant metrics via measures (in my example they are simple SUM measures.
I've attached the sample PBIX file
- naoyixue14 years ago
Post Patron
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!
- naoyixue14 years ago
Post Patron
Any thoughts? Thanks。
- PaulDBrown4 years ago
Community Champion
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