Need help on custom report

Community Support

5 years ago

Hi Anonymous ,

To my understand, the expected result is like below. If there is any misunderstanding, please let me know.

Summary	Dist Count of Cust who purchased >0
	purchase in launch month	From next month of launch month onwards to date
zone	1 Time Purchase	2 time purchase	3 time purchase	>3 time purchase	Total
North	3	1	1	1	6
East	2	0	1	2	5

I UnPivot your data and do some transformations. Please check the attached .pbix file.

Count = 
SWITCH (
    MAX ( Times[Time] ),
    "1",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Cust] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Zone] ),
                'Table'[Sales] > 0
                    && 'Table'[launch month]
            )
        ),
    ">3",
        VAR t =
            FILTER (
                SUMMARIZE (
                    'Table',
                    'Table'[Zone],
                    'Table'[Cust],
                    "MonthCount_",
                        CALCULATE (
                            DISTINCTCOUNT ( 'Table'[Month] ),
                            FILTER (
                                ALLEXCEPT ( 'Table', 'Table'[Zone], 'Table'[Cust] ),
                                'Table'[Sales] > 0
                                    && NOT ( 'Table'[launch month] )
                            )
                        )
                ),
                [MonthCount_] > 3
            )
        RETURN
            COUNTAX ( t, [Cust] ),
    VAR t =
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Zone],
                'Table'[Cust],
                "MonthCount_",
                    CALCULATE (
                        DISTINCTCOUNT ( 'Table'[Month] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[Zone], 'Table'[Cust] ),
                            'Table'[Sales] > 0
                                && NOT ( 'Table'[launch month] )
                        )
                    )
            ),
            [MonthCount_] = VALUE ( MAX ( Times[Time] ) )
        )
    RETURN
        COUNTAX ( t, [Cust] )
)

Count 2 = 
SUMX ( VALUES ( Times[Time Purchase] ), [Count] ) + 0

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need help on custom report.pbix55 KB

Forum Discussion