locked
PowerPivot - How to build this chart RRS feed

  • Question

  • Hi all,

    I am new to Power Pivot and I have been stuck with this.

    Given this basic dimensional model:

    [Dim Store] --> [Fact Table] <--[Dim Product]

    And supposing that:
    1. The Fact table is 'Factless' as only counts number of sale events by store, product and vendor;
    2. The Fact table has the degenerated dimension vendorID.
    3. Vendors sell products in different stores.

    How to build a PowerPivot chart to show the relation below?

    total (count of) vendorsID that sold the product 'P1' in the store 'S1' who also sold the product 'P2' in the store 'S2'?

    Can PowerPivot do that?

    Cheers,

    Juan



    • Edited by J F V Saturday, July 28, 2012 8:08 AM
    Saturday, July 28, 2012 6:37 AM

Answers

  • Hi Juan,

    To create a chart showing this figure, you must first create a measure reflecting that count.  Try a calculation like this one:

    countrows(
    	Filter(
    		summarize(
    			values ( Facts[vendor id] ),
    			Facts[vendor id],
    			"Store1_Prod1", countrows( Filter(Facts, Facts[Store id] = 1 && Facts[Product id] = 1 ) ),
    			"Store2_Prod2", countrows( Filter(Facts, Facts[Store id] = 2 && Facts[Product id] = 2 ) )
    		),
    	[Store1_Prod1] <> Blank() && [Store2_Prod2] <> Blank())
    )




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Elvis Long Wednesday, August 1, 2012 10:05 AM
    Wednesday, August 1, 2012 1:12 AM
    Answerer

All replies

  • Hi Juan,

    To create a chart showing this figure, you must first create a measure reflecting that count.  Try a calculation like this one:

    countrows(
    	Filter(
    		summarize(
    			values ( Facts[vendor id] ),
    			Facts[vendor id],
    			"Store1_Prod1", countrows( Filter(Facts, Facts[Store id] = 1 && Facts[Product id] = 1 ) ),
    			"Store2_Prod2", countrows( Filter(Facts, Facts[Store id] = 2 && Facts[Product id] = 2 ) )
    		),
    	[Store1_Prod1] <> Blank() && [Store2_Prod2] <> Blank())
    )




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by Elvis Long Wednesday, August 1, 2012 10:05 AM
    Wednesday, August 1, 2012 1:12 AM
    Answerer
  • Hi Javier,

    Thanks for your reply. It worked as you suggested.

    Thanks!

    Wednesday, August 1, 2012 9:55 AM