Forum Discussion

ArashZ's avatar
ArashZ
Helper I
3 years ago

calculated column

Hi,

 

I have a table like the below:

There are some companies on the table and each one sells different products. As can be seen, in the table, if the product code is glasses or shipping or additional item, the plan frequency column is monthly; otherwise is yearly. I want to create a new plan frequency modified column in a way that if a car or pen or pencil exists for that company, all the plan frequencies for that company change to yearly. The new desired common is also shown.

 

Company NamePlan frequencyProductcode
AMonthlyglasses
AMonthlyshipping
AYearlycar
AYearlypen
BMonthlyglasses
BYearlypen
BYearlypencil
BYearlybottle
CMonthlyglasses
CMonthlyshipping
CYearlycar
CYearlypen
CMonthlyadditional item

 

 

7 Replies

  • AnthonyJoseph's avatar
    AnthonyJoseph
    Resolver III
    3 years ago

    Hi ArashZ 

    I think this is what you are looking for...

    Plan Frequency Modified = 
var _isinlist = CALCULATE(COUNT('Table'[Productcode]),CONTAINSROW({"car","pen","pencil"},'Table'[Productcode]),FILTER(ALLEXCEPT('Table','Table'[Company Name]),'Table'[Company Name] = EARLIER('Table'[Company Name])))
return
if(_isinlist >=1,"Yearly", "Monthly")

     

    Hope this helps!

     

    Thanks, 

    AnthonyJoseph

    • ArashZ's avatar
      ArashZ
      Helper I
      3 years ago

      AnthonyJoseph 

       

      Thanks, but I received this error.

      A single value for the column company name in the table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation to get a single result.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    CalculatedColumn=VAR _c=Data[Company Name] RETURN IF(COUNTROWS(FILTER(Data,Data[Company Name]=_c&&Data[Productcode] IN {"car","pen","pencil"})),"Yearly",Data[Plan frequency])

    • ArashZ's avatar
      ArashZ
      Helper I
      3 years ago

      wdx223_Daniel 

       

      Thank you, but the solution doesn't work. The table still shows some monthly values.

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        3 years ago

        Hi, ArashZ 

        If wdx223_Daniel 's formula doesn't work, can you show relevant screenshots to further investigate what's causing the error?

        Also, check if the bias in the results is caused by errors in the data itself.

        Best Regards,
        Community Support Team _ Eason