Calculated Column

[Anonymous]

Hi Guys,

Please refer the below  table where I have the first 2 columns in my report and the 3rd is the desired column when the Annual Usage is divided by 1000 where ever type name is Electric. 

Can you help me with the measure to get this?

I've tried calculate function but its not helping me. Below is the expression used by me:

Annual Usage (MWh) = CALCULATE(SUM(Deals[AnnualUsage]),FILTER(Deals,Deals[typename]="Electric"))/1000

What should I use instead of sum to make it work?

Regards,

Himanshu

Annual Usage	Typename	Desired Output Column: Annual Usage (MWh)
1614892	Electric	1615
100000	Gas	0
175400	Electric	175.4
2356765	Gas	0
16877654	Electric	16878

[Anonymous]

@amitchandak Hi Amit,

Actually there is an issue with the column I created.

Please refer the below table:

Annual Usage	Typename	Desired Output Column: Annual Usage (MWh)	Unit
1614892	Electric	1615	kwh
100000	Gas	0	THM
175400	Electric	175400	MWh
2356765	Gas	0	THM
16877654	Electric	16878	kwh

I actually want the desired output column for the unit kwh only. I dont want values for "Unit" MWh to change.

What can be done in this scenario?

new column = if([typename]="Electric", round([AnnualUsage]/1000.0,0), 0) +0 : I want a filter where Unit = kwh in my calculation.

I'm basically converting only kwh values to MWh.

Regards,

Himanshu

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

Annual Usage (MWh) = round(CALCULATE(SUM(Deals[AnnualUsage]),FILTER(Deals,Deals[typename]="Electric"))/1000.0,0)

Or new column like

new column = if([typename]="Electric", round([AnnualUsage]/1000.0,0), 0) +0 

[Anonymous]

Thanks for your help !!!

The below solved my issue

new column = if([typename]="Electric", round([AnnualUsage]/1000.0,0), 0) +0 

Regards,

Himanshu

[aj1973]

Hi @Anonymous 

the measure is correct if you want to see the aggregation filtered by Electric in a Card Visual not in the Table