Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Categorize Cargo Destination based on Previous Historical Data

Hello all, 

 

I require an assistance regarding a categorization calculated column I am creating. I am trying to do this in DAX, but if there are any workareound, it would be great.

 

Below is my issue, I have 3 columns in the dataset, cargo , customer name and location. I would like to create another column that:

 

1. would repeat the Location if not Blank

2. If blank , would analyze how many times a location was used for the same customer, rank it and assign the most used one.

3. In case that there are customers that had same number of cargoes sent to different locations , choose one based on the alphabetical sequence (A-Z)

 

CargoCustomer NameLocationDesired Calculated Column
AMarkAsiaAsia
BMarkAsiaAsia
CMarkEuropeEurope
DMarkBlankAsia (based on the other data, Asia was the highest sent Location)
ELouisAsiaAsia
FLouisAsiaAsia
GLouisBlankAsia (based on the other data, Asia was the highest sent Location)
HJulieEuropeEurope
IJulieAmericaAmerica
JJulieBlankAmerica (it is 50 50 for America and Europe, so consider the first one (based on A-Z)

 

Thank you in advance!

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Anonymous create this calculated column:

     

    Column = 
IF(
    'Table'[Location] <> "", 'Table'[Location],
    VAR _current_customer = 'Table'[Customer Name]
    VAR _tbl = 
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('Table'[Location]),
            "@Cargos", CALCULATE(COUNTROWS('Table'))
        ),
        'Table'[Customer Name] = _current_customer,
        'Table'[Location] <> "",
        REMOVEFILTERS('Table')
    )
    VAR _max_cargos = MAXX(_tbl, [@Cargos])
    VAR _filtered_tbl = FILTER(_tbl, [@Cargos] = _max_cargos)
    VAR _remove_equals = TOPN(1,_filtered_tbl, 'Table'[Location] , ASC)
    VAR _result = CONCATENATEX(_remove_equals, 'Table'[Location])
    RETURN
        _result
)

     

     





          

    Showcase Report – Contoso By SpartaBI

1 Reply

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Anonymous create this calculated column:

     

    Column = 
IF(
    'Table'[Location] <> "", 'Table'[Location],
    VAR _current_customer = 'Table'[Customer Name]
    VAR _tbl = 
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('Table'[Location]),
            "@Cargos", CALCULATE(COUNTROWS('Table'))
        ),
        'Table'[Customer Name] = _current_customer,
        'Table'[Location] <> "",
        REMOVEFILTERS('Table')
    )
    VAR _max_cargos = MAXX(_tbl, [@Cargos])
    VAR _filtered_tbl = FILTER(_tbl, [@Cargos] = _max_cargos)
    VAR _remove_equals = TOPN(1,_filtered_tbl, 'Table'[Location] , ASC)
    VAR _result = CONCATENATEX(_remove_equals, 'Table'[Location])
    RETURN
        _result
)

     

     





          

    Showcase Report – Contoso By SpartaBI