Top 10 cities basis values chosen on slicers

[monojchakrab]

Hi good people,

I am working with a fact table as below :

account	date	month	year	title	quantity	value	MPU	active	format	sizes	volume	unit price	zipcode	city	state
amazon	Sunday, 14 April, 2024	April	2024	Equal® Classic Jar (100g) | Zero Calorie Sweetener | No Aspartame | Diet Friendly |Table Top Sweetener | Powder Formulation | Value Pack | Pack of 1	2	300	1	Sucralose	Jar	100 gm	2	150	700074	KOLKATA	West Bengal
amazon	Wednesday, 3 April, 2024	April	2024	Equal Stevia Natural Sweetener, Sugar Free, 50 Sachet, Pack of 1	2	300	1	Stevia	Sachets	50	2	150	741159	BIRNAGAR	West Bengal
amazon	Sunday, 31 March, 2024	March	2024	Equal Stevia Natural Sweetener, Sugar Free, 50 Sachet, Pack of 1	2	300	1	Stevia	Sachets	50	2	150	110096	NEW DELHI	Delhi
amazon	Monday, 20 May, 2024	May	2024	Equal® Stevia Plant-Based 100% Natural Sweetener | Sugar Free | Diabetic Friendly | Vegan & Keto Friendly | 300 Tablets | Pack of 1 (Pack of 3)	2	1800	1	Stevia	Tablets	300	2	900	695010	THIRUVANANTHAPURAM	Kerala
amazon	Wednesday, 1 November, 2023	November	2023	Equal® Stevia 500 Tablets | Plant-Based Natural Sweetener | 100% Natural Sweetness from Stevia | Zero Calorie from Stevia | Tastes Like Sugar | Ideal	2	610	1	Stevia	Tablets	500	2	305	768001	SAMBALPUR	Odisha

What I am trying to achieve is as follows :

Depending on the combination of the values chosen on the slicers, it should return a table of top 10 cities basis purchase frequency :

The measure I am working with is as below :

Top 10 Cities = 
VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR FilteredCities = 
    FILTER(
        VALUES('ecomm consolidated'[city]),
        'ecomm consolidated'[city] <> "xxxx"
    )
VAR PurchaseFrequencyTable =
    ADDCOLUMNS(
        FilteredCities,
        "Purchase Frequency", 
        CALCULATE(
            COUNTROWS('ecomm consolidated'),
            'ecomm consolidated'[Format] = _selectedFormat,
            'ecomm consolidated'[Active] = _selectedActive,
            'ecomm consolidated'[Sizes] = _selectedPack
        )
    )
// VAR RankedTable =
//     ADDCOLUMNS(
//         PurchaseFrequencyTable,
//         "Rank", 
//         RANKX(
//             PurchaseFrequencyTable,
//             [Purchase Frequency],
//             ,
//             DESC,
//             DENSE
//         )
//     )
VAR Top10Cities =
    TOPN(
        10,
        PurchaseFrequencyTable,
        [Purchase Frequency], DESC
    )
RETURN
    SELECTCOLUMNS(
        Top10Cities,
        "City", [city],
        "Purchase Frequency", [Purchase Frequency]
    )
    

But as shown above in the screenshot, it returns a blank table - not sure what am I missing. I tested the selectedvalue measures separately and those work. But the purchasefrequencytable does not return. The list of cities does not filter to T10 and if I add the purchase frequency, the table turns blank.

Any help much appreciated

Best regds.,

[monojchakrab]

I am continuing on this thread as my existing question still remains unresolved. While trying to debug the code, I found the following :

1. When I am forcing the filter to choose the parameters manually as below : 

Top cities = 
// VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
// VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
// VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR Table_purchase_frequency =

SUMMARIZE(
    CALCULATETABLE(
        'ecomm consolidated',
        FILTER(
            'ecomm consolidated',
            'ecomm consolidated'[city] <> "xxxx" &&
            'ecomm consolidated'[active] = "aspartame" &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[format] = "tablets" &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[sizes] = "100"  -- Replace with slicer selection if needed
        )
    ),
    'ecomm consolidated'[city],
    "Purchase Frequency", COUNTROWS('ecomm consolidated')
)

RETURN

TOPN(10,

Table_purchase_frequency,

[Purchase Frequency],

DESC)

It seems to return the correct table, even though its not sorted in descending order as it should have.

2. But when I am trying to feed the filter function from the values chosen dynamically from the slicers on the page, it returns a blank table.

Top cities = 
VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

VAR Table_purchase_frequency =

SUMMARIZE(
    CALCULATETABLE(
        'ecomm consolidated',
        FILTER(
            'ecomm consolidated',
            'ecomm consolidated'[city] <> "xxxx" &&
            'ecomm consolidated'[active] = _selectedActive &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[format] = _selectedFormat &&  -- Replace with slicer selection if needed
            'ecomm consolidated'[sizes] = _selectedPack -- Replace with slicer selection if needed
        )
    ),
    'ecomm consolidated'[city],
    "Purchase Frequency", COUNTROWS('ecomm consolidated')
)

RETURN

TOPN(10,

Table_purchase_frequency,

[Purchase Frequency],

DESC)

 

3. But when I checked the slicers individually,

here's the code : 

Slicer selection = 
    VAR _selectedFormat = SELECTEDVALUE('ecomm consolidated'[format])
    VAR _selectedActive = SELECTEDVALUE('ecomm consolidated'[active])
    VAR _selectedPack = SELECTEDVALUE('ecomm consolidated'[sizes])

RETURN

    _selectedActive & "-" & _selectedFormat &"-" & _selectedPack

Is there something I am missing? any help appreciated.

appreciate and best regds.,

[Anonymous]

Hi @monojchakrab 

I am a little confused about your request. What is the logic for calculating the frequency of purchases? Is it counting the number of rows based on the selections in the slicer? And then display the top 10 purchase frequency?

Best Regards,
Yulia Xu

[monojchakrab]

@Anonymous - I want to return the T10 cities, based on the purchase frequency of the combination of the values on the slicers...e.g. which are the T10 cities for Stevia 100 tablets, basis the purchase freuqncy of this combination chosen from the slicers. Similalry, if I change the slicer figures to stevia 100 sachets, it should return the T10 cities for than combination. If I get this code right, I can then toggle from purchase fequency to volume or value too. 

Does that answer your question?