Forum Discussion

naoyixue1's avatar
naoyixue1
Icon for Post Patron rankPost 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's avatar
    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 

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
4 years ago

Hi naoyixue1.  Why are you trying to combine them into a new table?  Is this for display purposes only?

naoyixue1's avatar
naoyixue1
Icon for Post Patron rankPost 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. 

 

 

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi naoyixue1.  You definitely don't need to create another table to do this.  Just a couple of measures.

     

    Can you provide a sample pbix so I can work with it?