Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DaX Calculation

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
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

     

     

7 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the suggestion.

       

      It resolved my issue 🙂

       

      Regards,

      Himanshu

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        amitchandak
        Super User
        5 years ago

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