DaX Calculation

[Anonymous]

Hi,

I'm trying to use the below formula but its not giving me the correct data.

Annual Usage (MWh) = IF (

[typename] = "Electric" && [Unit] in{"mWh"},

ROUND ( [AnnualUsage] * 1000.0, View_DealClosing[AnnualUsage] )+0)

I have 2 units in my Unit column i.e mWh and kWh. I want when the type is electric and unit in mwh then the usage should get multiplied by 1000 and rest should remain same which means all the other usage with kwh unit should remain as it is. Only the usage with unit mwh should change.

What is the correct formula for this approach?

Regards,

Himanshu

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can create a Calculated column or Measure.

1 Calculated column

Annual Usage (MWh) =

IF (

    [Type Name] = "Electric"

        && [Unit] IN { "mWh" },

    ROUND ( [Annual Usage] * 1000, 0 ),

    IF ( [Type Name] = "Electric", ROUND([Annual Usage],0), 0 )

)

2 Measure

Annual Usage (MWh)_measure =

VAR type_name =

    SELECTEDVALUE ( View_DealClosing[Type Name] )

VAR unit =

    SELECTEDVALUE ( View_DealClosing[Unit] )

VAR usage =

    SELECTEDVALUE ( View_DealClosing[Annual Usage] )

RETURN

    IF (

        type_name = "Electric"

            && unit IN { "mWh" },

        ROUND ( usage * 1000, 0 ),

        IF ( type_name = "Electric", ROUND(usage,0), 0 )

    )

The result looks like this:

Fore more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the suggestion.

It resolved my issue 🙂

Regards,

Himanshu

[amitchandak]

@Anonymous , Try like

Annual Usage (MWh) = IF (
[typename] = "Electric" && [Unit] in{"mWh"},
round([AnnualUsage] * 1000.0,0), View_DealClosing[AnnualUsage] +0)

Choose correct place for round as per need. I used 0

[Anonymous]

Annual Usage (MWh) = IF (
[typename] = "Electric" && [Unit] in{"mWh"},
round([AnnualUsage] * 1000.0,0), View_DealClosing[AnnualUsage] +0)

This is also not giving correct figures. Whereever there is no deal for typename = electric there also annual usage is coming which should not be the case as that usage is for other typename i.e Nat Gas.

Refer the above screenshot. The annual usage(KwH) should be 0 here as there is 0 deal for electric but it is showing data for other typename. I think it has to do ith the AnnualUsage being used in the formula.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Please refer above table. I want the data to be convereted where typename = electric and unit in mwh only. 

[Anonymous]

Also, evertime I try to send a table output it gives me an error . HTML error.

WHat should I do and when I try to insert a file I asks for a URL link. Can't i insert an excel file here?