IF statement - problem - need at row level and not calculated column

[Anonymous]

Hello

I need to figure out how to get an If statement to work at the row level (iteration per row).  It cannot be a calculated column, it needs to be a measure.

The simple if statement (that does not work) is:  If ('Sales History'[Attribute]= "MMBTU", [Actual MMBTU],[Actual/MMBTU])

Sales History is sales data and has many rows with attribute "MMBTU", so the formula errors out.

There are several other Attributes such as Revenue, Transportation, Lease, etc.

All our reports need to show the metric (Actual/MMBTU) per attribute.

I understand that I should aggregate in order for it to work but it is incorrect to sum the metric (Actual/MMBTU).  

Is there an aggregate that does not actual aggregate the calculations? LOL

Just FYI: I have to get this to work  Actual and Forecast for MTD, YTD, Estimated YE.

Related measures:

Actual MMBTU = CALCULATE(SUM('MMBTU'[Total]),FILTER(values(MMBTU[Version]),'MMBTU'[Version]="Actual"))

Actual Sales = CALCULATE(SUM('Sales History'[Total]),FILTER(values('Sales History'[Version]),'Sales History'[Version]="Actual"))

Actual/MMBTU = DIVIDE([Actual Sales],[Actual MMBTU],0)

Other information that may help

The MMBTU table and the Sales History table are indirectly related by Customer, Date, Attribute, and Version

The Sales History table does have the same MMBTU data as the MMBTU table.  I separated it so that I could format it differently; MMBTU is reported without decimal and the metric is $0.  

I cannot share the actual information due to company rules

[v-juanli-msft]

Hi @Anonymous

With a little modifiaction of your formula, I can use the measure correctly.

Please have a look at my test, if you have any problem in your scenario, feel free to ask me.

measure = If (MAX('Sales History'[Attribute])= "MMBTU", [Actual MMBTU],[Actual/MMBTU])

Best Regards

Maggie

[Anonymous · Accepted solution]

Thank you very much.  It is so obvious now LOL  It is very time consuming to learn this through you tube so I greatly appreciate the help

[Seward12533]

First best practice is to sue DIVIDE function rather than arithmatic /  but that is not your problem. Why you can't share actual data you shoudl be able to show snip of the table layout and relatship map and at least table layouts. Ideally you could make up some quick dummy tables with a few rows of dummy data and share that. Some idea of Actual vs Expected results may help as you may just have a fulter context / relationship issue.

Why is it incorrected to aggregate a SUM? I agree its not cool to aggregte individual percentags (but you can get around that with aggregated sums and counts. Sounds to me that you might be overcomplicating the model.  I have very complex Sales history reprots with attributes but Budget, Forecast and Actual and have no problem doing Montly, YTD, QTD, YOY, Variances etc.. without ever using SUMX to iterate on rows.

But short answer is if you need row level iteration you will need to use SUMX but a calcualated column that you can use to filter out rows easily may make some of your measures easier and potentially avoid using SUMX vs SUM.