Forum Discussion

ishwariya11195's avatar
ishwariya11195
Frequent Visitor
1 year ago
Solved

Calculated column which gives dynamic results

I have the follwoing table which a fact table but I have reduced the number of columns here. 

The goal is to have the calculated columne "Calculated country name" which does the follwoing:

 

If the number of manufacturers are less than 3 for a selected year in the filter, the column should evlaute the consition for selected year and selected year -1 ,if number of manufactres is less than 3, if yes , "Others", else "Country name".

 

for example, if 2023 is slected, it evaulated the coniditon for 2023 and 2022 , gives the results.

2024 is sleeected, it evaluated for 2024 and 2023.

 

the below formula is working if hardcoded but not when applied selected filters. kindly help

 

PBI Calc Country Name =
VAR MinTotalSellers =
    MINX(
        FILTER(
            'PBI Fact Others',
            'PBI Fact Others'[Country] = EARLIER('PBI Fact Others'[Country]) &&
                    'PBI Fact Others'[Product Category] = EARLIER('PBI Fact Others'[Product Category]) &&
            ('PBI Fact Others'[Year] = 2022 || 'PBI Fact Others'[Year] = 2023)
        ),
        'PBI Fact Others'[Total Sellers]
    )
RETURN
    IF(MinTotalSellers < 3, "Others", 'PBI Fact Others'[Country])

 

CountryProduct nameNo.of.Manufacturers Calculated country name
IndiaTrolley12022Others
MalasyiaFrame22022Others
IndiaTrolley42023Others
MalasyiaFrame42023Others
IndiaTrolley 52024India
MalasyiaFrame62024Malasyia
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi ishwariya11195  - can you please check the below measure based on selected filter, hope it works.

     

    PBI Calc Country Name =
    VAR SelectedYear = MAX('Mfyear'[Year])
    VAR Previou = SelectedYear - 1

    VAR ManufacturersCurrentYear =
        CALCULATE(
            DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),
            'Mfyear'[Year] = SelectedYear
        )

    VAR ManufacturersPreviousYear =
        CALCULATE(
            DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),
            'Mfyear'[Year] = Previou
        )

    VAR ManufacturersTotal = ManufacturersCurrentYear + ManufacturersPreviousYear

    RETURN
        IF(ManufacturersTotal < 3, "Others", MAX('Mfyear'[Country]))

     

     

    I hope this works.

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi ishwariya11195  - can you please check the below measure based on selected filter, hope it works.

     

    PBI Calc Country Name =
    VAR SelectedYear = MAX('Mfyear'[Year])
    VAR Previou = SelectedYear - 1

    VAR ManufacturersCurrentYear =
        CALCULATE(
            DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),
            'Mfyear'[Year] = SelectedYear
        )

    VAR ManufacturersPreviousYear =
        CALCULATE(
            DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),
            'Mfyear'[Year] = Previou
        )

    VAR ManufacturersTotal = ManufacturersCurrentYear + ManufacturersPreviousYear

    RETURN
        IF(ManufacturersTotal < 3, "Others", MAX('Mfyear'[Country]))

     

     

    I hope this works.