Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Virtual Table to classify Chanels

Hello!

I have a data base with the following columns (also attached in this link: DB PBI )

YearQuarterWeekCountryProduct GroupProduct Sub-GroupSalesQuantityChannelsStock

 

I need to classify the chanels in 2: Big chanels and Small chanels

 

Big Chanels sold more than 25K in the past 3 Quarters

Small Chanels sold less than 25K in the past 3 Quarter

 

I need to create a slicer to filter my report by Big Chanels or Small Chanels

 

How can I do it?

 

Thanks in advance!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    The following output is like your xlsx,but not roll up:

    And if you need roll up,see the below:

     

    Import data ,then create change many column form text to whole number ,then create the below column ,and measure:

    sumsale = CALCULATE(SUM(DB1[Sales]),FILTER(ALL(DB1),DB1[Year]=MAX(DB1[Year])&&DB1[Quarter]=MAX(DB1[Quarter])&&DB1[Channels]=MAX(DB1[Channels])&&DB1[Stock]="Not Stock"))

    column:

    sumsale1 = 
CALCULATE (
    SUM ( DB1[Sales] ),
    FILTER (
        ALL ( DB1 ),
        DB1[Year] = EARLIER ( DB1[Year] )
            && DB1[Quarter] = EARLIER ( DB1[Quarter] )
            && DB1[Channels] = EARLIER ( DB1[Channels] )
            && DB1[Stock] = "Not Stock"
    )
)

    measure:

    Channel = 
IF (
    MAX ( DB1[sumsale1] ) >= 25000
        && CALCULATE (
            MAX ( DB1[sumsale1] ),
            FILTER (
                ALL ( DB1 ),
                DB1[Channels] = MAX ( DB1[Channels] )
                    && IF (
                        MAX ( DB1[Quarter] ) >= 2,
                        DB1[Quarter]
                            = MAX ( DB1[Quarter] ) - 1
                            && DB1[Year] = MAX ( DB1[Year] ),
                        DB1[Quarter] = 4
                            && DB1[Year]
                                = MAX ( DB1[Year] ) - 1
                    )
            )
        ) >= 25000
        && CALCULATE (
            MAX ( DB1[sumsale1] ),
            FILTER (
                ALL ( DB1 ),
                DB1[Channels] = MAX ( DB1[Channels] )
                    && IF (
                        MAX ( DB1[Quarter] ) >= 3,
                        DB1[Quarter]
                            = MAX ( DB1[Quarter] ) - 2
                            && DB1[Year] = MAX ( DB1[Year] ),
                        IF (
                            MAX ( DB1[Quarter] ) = 2,
                            DB1[Quarter] = 4
                                && DB1[Year]
                                    = MAX ( DB1[Year] ) - 1,
                            DB1[Quarter] = 3
                                && DB1[Year]
                                    = MAX ( DB1[Year] ) - 1
                        )
                    )
            )
        ) >= 25000,
    "Big Channel",
    "Small channel"
)

     

    And you could download my pbix file if you need!

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

4 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    The following output is like your xlsx,but not roll up:

    And if you need roll up,see the below:

     

    Import data ,then create change many column form text to whole number ,then create the below column ,and measure:

    sumsale = CALCULATE(SUM(DB1[Sales]),FILTER(ALL(DB1),DB1[Year]=MAX(DB1[Year])&&DB1[Quarter]=MAX(DB1[Quarter])&&DB1[Channels]=MAX(DB1[Channels])&&DB1[Stock]="Not Stock"))

    column:

    sumsale1 = 
CALCULATE (
    SUM ( DB1[Sales] ),
    FILTER (
        ALL ( DB1 ),
        DB1[Year] = EARLIER ( DB1[Year] )
            && DB1[Quarter] = EARLIER ( DB1[Quarter] )
            && DB1[Channels] = EARLIER ( DB1[Channels] )
            && DB1[Stock] = "Not Stock"
    )
)

    measure:

    Channel = 
IF (
    MAX ( DB1[sumsale1] ) >= 25000
        && CALCULATE (
            MAX ( DB1[sumsale1] ),
            FILTER (
                ALL ( DB1 ),
                DB1[Channels] = MAX ( DB1[Channels] )
                    && IF (
                        MAX ( DB1[Quarter] ) >= 2,
                        DB1[Quarter]
                            = MAX ( DB1[Quarter] ) - 1
                            && DB1[Year] = MAX ( DB1[Year] ),
                        DB1[Quarter] = 4
                            && DB1[Year]
                                = MAX ( DB1[Year] ) - 1
                    )
            )
        ) >= 25000
        && CALCULATE (
            MAX ( DB1[sumsale1] ),
            FILTER (
                ALL ( DB1 ),
                DB1[Channels] = MAX ( DB1[Channels] )
                    && IF (
                        MAX ( DB1[Quarter] ) >= 3,
                        DB1[Quarter]
                            = MAX ( DB1[Quarter] ) - 2
                            && DB1[Year] = MAX ( DB1[Year] ),
                        IF (
                            MAX ( DB1[Quarter] ) = 2,
                            DB1[Quarter] = 4
                                && DB1[Year]
                                    = MAX ( DB1[Year] ) - 1,
                            DB1[Quarter] = 3
                                && DB1[Year]
                                    = MAX ( DB1[Year] ) - 1
                        )
                    )
            )
        ) >= 25000,
    "Big Channel",
    "Small channel"
)

     

    And you could download my pbix file if you need!

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That is genius, thank you very much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello VahidDM , I don't understand what the expected output is, can you explain me how to get it?