Forum Discussion

Post Patron

4 years ago

Solved

how to summarize one table whose data are from two fact tables

Hey there, Let me briefly introduce the background as below. Originally, we only calculate the difference in units and % (production against forecast) as our metrics. But it might mislead us,...

Need Help

PaulDBrown

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

Sample.pbix49 KB

naoyixue1

Post Patron

4 years ago

littlemojopuppy Hey, thanks for your checking. If I cannot combine each item's forecast and production with vendor together, I'm not sure how I can calcualte the measure about how many items we have forecast per each vendor and the related hit rate per item. Do you know that ? Yes. It's just for display. (Because Item and Vendor are in separate lookup table). If I don't combine them, what kind of measre I need to write? Would you give me some of your thoughts? Really appreciate that.