Cannot convert value 'comedy specialOCM' of type text to type true / False

[snandy2011]

Hi All,

I am struggleing with a simple Dax formula which can easily done in excel but getting some problem on power bi. I just want to calculate maximum profit of all account. I have datasets like,

Account           Profit

A                        100

B                         100

A                         200

C                         100

D                         200

B                         100

D                         50 

so, here, maximum profit is belonging to A, which is 300. This simple thing I want to do by DAX. I have done this in excel. Written following formula in Dax 

Max Profit = CALCULATE( MAX(' Profit'[Gross Revenue]), FILTER('Profit','Profit'[ Account] ) )

but getting Cannot convert value 'comedy specialOCM' of type text to type true / False error.

can you please suggest me how to fix this formula and error?

Any suggesation is really appreciable.

Thanks,

snandy2011

[Seward12533]

Why not just use

  Max Profit = MAX(' Profit'[Gross Revenue])

It should then dynamically caluclate in contex whenever you use it. So if you built a table with Account and Profit it should show the max profit by account.

The Summarize solution is nice it builds a table you can reference and link to your model.

[snandy2011]

Hi @Seward12533

If i use only Max Profit = MAX(' Profit'[Gross Revenue]). It's only giving me the maximum profit of all user. Check out my exapmple on question section. If i apply your formula only, it will only give me maximum profit of all user, which is 200.But from A we get profit multiple times, from B as well. So the maximum profit will be 300, which is belonging to A. So,only use the Max formula will not be the solution.

Thnaks,

snandy2011

[Seward12533]

Sorry it was late and I didn't really read your message. PowerBI/PowerPivot is a paradigm shift from classic excel. In the Example above if add a visual could be Matrix, Bar Chart, or whatever the application will figure out the context and show it correctly

Assuming you have sales data you will need a few measures

  Revenue = SUM(sales[Total Sales])
  Cost = SUM(sales[COGS])

  Margin = ([Revenue]-[Cost])/[Revenue]

If you want a dedicated measure to display the Margin for one of the accounts independently you could use the calculate with filter

Margin A = CALCULATE([Margin],sales[Account]="A")  which is really short for  CALCULATE([Margin],FILTER(sales,sales[Account]=A))

[snandy2011]

@Seward12533 Yes. You are right. On First time, I followed  your process, but i was getting above mentioned error.So, therefore,i post this question over here.Anyway, I have solved this problem.Although this is not the right process to do, but it shows the right result.

Thanks once again for your reply and valueable suggesation.

Thanks,

snandy2011

[v-jiascu-msft · Accepted solution]

Hi @snandy2011,

There could be two possible causes.

1.  FILTER('Profit','Profit'[ Account] ). The second parameter of FILTER should be a condition rather than a column.

2. What's 'Profit'[Gross Revenue]?  A measure or a calculated column?

The measure could be like below.

result =
MAXX (
    SUMMARIZE ( 'table', 'table'[Account], "totalProfit", SUM ( 'table'[Profit] ) ),
    [totalProfit]
)

Best Regards,

Dale

[snandy2011]

Hi @v-jiascu-msft

Sorry for late replying..Profit'[Gross Revenue] is measure.

I am going through your solution.It it solves i will let you know.

Thank you very much for your suggesation.

Thanks,

snandy2011