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 th...
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.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper 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.