NUMERIC DISTRIBUTION - MEASURE ISSUE

[mc_gadhzalova]

Hi All,

I have been struggling to create a measure to calculate numeric distribution. 
I need measure/s that calculate if a brand is avaliable in a store and then compared to the total number stores. 

My data is structured as it follows:

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

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:

COUNTRY	MONTH	STORE	BRAND	BRAND DISTRIBUTION
BG	1	X	ENERGY DRINK	1
BG	1	X	COFFEE	0
BG	1	Y	ENERGY DRINK	0
BG	1	Y	COFFEE	1
BG	2	X	ENERGY DRINK	1
BG	2	X	COFFEE	1
BG	2	Y	ENERGY DRINK	0
BG	2	Y	COFFEE	0

I managed to reach this result with the use of the formula 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.

Thank you very much in advance!

Michaela

[v-yiruan-msft · Accepted solution]

Hi @mc_gadhzalova ,

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] )
                        && 'Table'[MONTH] = EARLIER ( 'Table'[MONTH] )
                        && 'Table'[STORE] = EARLIER ( 'Table'[STORE] )
                        && 'Table'[BRAND] = EARLIER ( 'Table'[BRAND] )
                        && 'Table'[EXISTS] = 1
                )
            )
    )
VAR _count =
    COUNTX ( _tab, [@count] )
RETURN
    IF ( _count > 0, 1, 0 )

Best Regards

[lbendlin]

Why does it have to be a measure? Can it be influenced by filters?