cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aguiardavid Frequent Visitor
Frequent Visitor

Calculated column based on measurements.

Please help me!

I'm needing a filter based on value in one measure.

I have a dimension "Client" which has 2 related tables. Sale and Potential. The relationship of these tables is 1 -> N.

I need to create a rating, being:

Has potential and has sale: OK
Has potential and has no sale: attention.

This needs to be done at the aggregate level (total client) and filter, in order to filter clients "Ok" and "Attention".

I made the measure calculated like this:
LegendMap = if ([qtd_cli_potential]> 0 && [qtd_cli_pot_vend]> 0; "Ok"; "Warning")

Setting up a table, placing the "Client" and this Map Legend field, works. However, I need to put it as a filter, where I can choose. And I can not add calculated measure to the filter.

I imagine the solution is to include a calculated column in the Customer dimension that makes the rule above. I tried to do, but it only makes over the total, brings the overall total to all customers. can you help me?
17 REPLIES 17
Highlighted
Super User
Super User

Re: Calculated column based on measurements.

Yes, add it as a calculated column in he client table. I see no reason why it won’t work if you copy it exactly as you have shown. If you are replacing the measures in the formula with the underlying formula, then you will need to add CALCULATE around the formulas. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
richbenmintz
Advisor

Re: Calculated column based on measurements.

Hi @aguiardavid,

 

As Matt explains, you need to wrap your sums in Calculate like so

 

Column = if(CALCULATE(sum(Table2[qtd_cli_potential]))>0 && CALCULATE(SUM(Table2[qtd_cli_pot_vend]))>0,
"Ok"; "Warning")

Richard

 

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.

dont work.

 

All rows "ok".

 

1 row ok... 2 row ok... 3 row ok.... but 4, 5 and 6 row no...

 

 

print.png

Ronald123 Member
Member

Re: Calculated column based on measurements.

Helle @aguiardavid,

 

Try this forumule;

 

Result = 
VAR Sales1 = SUM(Table1[Sales 1])
VAR Sales2 = SUM(Table1[Sales 2])
RETURN
IF( Sales1 && Sales2>0;
"OK";
"WARNING")

Naamloos.png

 

Greets,

 

Ronald

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.


@Ronald123 wrote:

Helle @aguiardavid,

 

Try this forumule;

 

Result = 
VAR Sales1 = SUM(Table1[Sales 1])
VAR Sales2 = SUM(Table1[Sales 2])
RETURN
IF( Sales1 && Sales2>0;
"OK";
"WARNING")

Naamloos.png

 

Greets,

 

Ronald


 

If calculated measure, ok.

 

but calculated column, no.

 

I just need to have a field where I can filter the "IDs" OK or Warning.

 

print.png

Ronald123 Member
Member

Re: Calculated column based on measurements.

Hello @aguiardavid,

 

Try this forumule in a calculated colum;

 

Result = 
VAR Sales1 = IF(Table1[Sales 1]<>0;1)
VAR Sales2 = IF(Table1[Sales 2]<>0;1)
VAR TotalScore = Sales1+Sales2
RETURN
IF(TotalScore=2;"OK";
"WARNING")

Greets,

 

Ronald

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.


@Ronald123 wrote:

Hello @aguiardavid,

 

Try this forumule in a calculated colum;

 

Result = 
VAR Sales1 = IF(Table1[Sales 1]<>0;1)
VAR Sales2 = IF(Table1[Sales 2]<>0;1)
VAR TotalScore = Sales1+Sales2
RETURN
IF(TotalScore=2;"OK";
"WARNING")

Greets,

 

Ronald


 

 

did not work either.

 

The problem is that both fields need to be aggregated, because the values ​​we saw are the sum.

 

When I create a calculated column the sum of a field is being the total of the table. see the image.

 

if I can get this value to get the filters and the "id", I believe it will work on the filter.

 

print.png

Ronald123 Member
Member

Re: Calculated column based on measurements.

@aguiardavid,

 

Check the file, the calculated colum can be used as filter.

 

https://1drv.ms/u/s!An5X7wdH9O9eiiM2VftWnjWfBTCK

 

Greets,

 

Ronald

 

 

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.


@Ronald123 wrote:

@aguiardavid,

 

Check the file, the calculated colum can be used as filter.

 

https://1drv.ms/u/s!An5X7wdH9O9eiiM2VftWnjWfBTCK

 

Greets,

 

Ronald

 

 


Ronald123
Thank you for your help.

The example you sent has no aggregation. I think that's why.

Imagine that "Sale 1" was a separate table, in which "radio" had another column, for example "color". I would have to add, to sort later.

The 2 tables I'm using have different granularities. So what I show in the table, is added by sum (in 2). In your case, there is no aggregation, so I believe it works.