Forum Discussion

mc_gadhzalova's avatar
mc_gadhzalova
Regular Visitor
2 years ago
Solved

NUMERIC DISTRIBUTION - MEASURE ISSUE

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:

 

COUNTRYMONTHSTOREBRANDITEMEXISTS
BG1XENERGY DRINKCLASSIC FLAVOUR1
BG1XENERGY DRINKAPPLE FLAVOUR

0

BG1XCOFFEELATTE0
BG1YENERGY DRINKCLASSIC FLAVOUR0
BG1YENERGY DRINKAPPLE FLAVOUR0
BG1YCOFFEELATTE1
BG2XENERGY DRINKCLASSIC FLAVOUR1
BG2XENERGY DRINKAPPLE FLAVOUR1
BG2XCOFFEELATTE1
BG2YENERGY DRINKCLASSIC FLAVOUR0
BG2YENERGY DRINKAPPLE FLAVOUR0
BG2YCOFFEELATTE0

 

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 DISTRIBUTION
BG1XENERGY DRINK1
BG1XCOFFEE0
BG1YENERGY DRINK0
BG1YCOFFEE1
BG2XENERGY DRINK1
BG2XCOFFEE1
BG2YENERGY DRINK0
BG2YCOFFEE0

 

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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