NUMERIC DISTRIBUTION - MEASURE ISSUE

Question

Hi All,&nbsp;I have been struggling to create a measure to calculate numeric distribution.&nbsp;I need measure/s that calculate if a brand is avaliable in a store and then compared to the total number stores.&nbsp;My data is structured as it follows:&nbsp;COUNTRYMONTHSTOREBRANDITEMEXISTSBG1XENERGY DRINKCLASSIC FLAVOUR1BG1XENERGY DRINKAPPLE FLAVOUR0BG1XCOFFEELATTE0BG1YENERGY DRINKCLASSIC FLAVOUR0BG1YENERGY DRINKAPPLE FLAVOUR0BG1YCOFFEELATTE1BG2XENERGY DRINKCLASSIC FLAVOUR1BG2XENERGY DRINKAPPLE FLAVOUR1BG2XCOFFEELATTE1BG2YENERGY DRINKCLASSIC FLAVOUR0BG2YENERGY DRINKAPPLE FLAVOUR0BG2YCOFFEELATTE0&nbsp;The logic is if there is atlest 1 item from each brand that exists in store, that there is brand distribution.And my desired outcome logic is here:COUNTRYMONTHSTOREBRANDBRAND DISTRIBUTIONBG1XENERGY DRINK1BG1XCOFFEE0BG1YENERGY DRINK0BG1YCOFFEE1BG2XENERGY DRINK1BG2XCOFFEE1BG2YENERGY DRINK0BG2YCOFFEE0&nbsp;I managed to reach this result with the use of the formula&nbsp;SUMMARIZE, but i couldn't move forward from this result to reach my desired calculation.Also, my dataset is very large and copying the whole data and grouping in it Power Query isn't an option.&nbsp;Thank you very much in advance!Michaela&nbsp;

anonymous · Accepted Answer

Hi&nbsp;mc_gadhzalova&nbsp;,
You can create a measure as below to get it, please find the details in the attachment.
BRAND DISTRIBUTION = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[COUNTRY],
        'Table'[MONTH],
        'Table'[STORE],
        'Table'[BRAND],
        "@count",
            CALCULATE (
                COUNT ( 'Table'[COUNTRY] )  , 
             FILTER (
                    'Table',
                    'Table'[COUNTRY] = EARLIER ( 'Table'[COUNTRY] )
                        &amp;&amp; 'Table'[MONTH] = EARLIER ( 'Table'[MONTH] )
                        &amp;&amp; 'Table'[STORE] = EARLIER ( 'Table'[STORE] )
                        &amp;&amp; 'Table'[BRAND] = EARLIER ( 'Table'[BRAND] )
                        &amp;&amp; 'Table'[EXISTS] = 1
                )
            )
    )
VAR _count =
    COUNTX ( _tab, [@count] )
RETURN
    IF ( _count &gt; 0, 1, 0 )

Best Regards

Forum Discussion

NUMERIC DISTRIBUTION - MEASURE ISSUE

Related content

PQ -Date Distribution

Calculate numeric distribution

Numeric distribution - How to create measure

Distribution Chart

Normal Distribution

COUNTRY	MONTH	STORE	BRAND	ITEM	EXISTS
BG	1	X	ENERGY DRINK	CLASSIC FLAVOUR	1
BG	1	X	ENERGY DRINK	APPLE FLAVOUR	0
BG	1	X	COFFEE	LATTE	0
BG	1	Y	ENERGY DRINK	CLASSIC FLAVOUR	0
BG	1	Y	ENERGY DRINK	APPLE FLAVOUR	0
BG	1	Y	COFFEE	LATTE	1
BG	2	X	ENERGY DRINK	CLASSIC FLAVOUR	1
BG	2	X	ENERGY DRINK	APPLE FLAVOUR	1
BG	2	X	COFFEE	LATTE	1
BG	2	Y	ENERGY DRINK	CLASSIC FLAVOUR	0
BG	2	Y	ENERGY DRINK	APPLE FLAVOUR	0
BG	2	Y	COFFEE	LATTE	0