Forum Discussion

timEalll's avatar
timEalll
Regular Visitor
3 years ago
Solved

Divide categories total by baseline total

I have a problem I need help with, I am still a newbie in Power BI.

 

I created Total measure to add scope1 and scope2.
The issue I am facing is the take the Total of the "Baseline 19/20" which is 1121 and use it to divide total of "Actual 20/21, Actual 21/22, Actual 22/23".
Hence, I want my result to be 

Please note if Actual 23/24 appears it will automatically do the calculation.
Can you help me please?

Need Help
Show and Tell
Tips and Tricks
  • SamInogic's avatar
    SamInogic
    3 years ago

    Hi,

     

    As per our understandings you are looking for a column based on some Conditions,
    Here what I have found for Reaching to your Requirement,

     

    Initially we have to Create Calculated column with below Dax Expression,


    Custom_Carbon =

    var Basline =
    IF('Sample table'[Carbon] = "Baseline 19/20",'Sample table'[Total],0)

    return
    Basline

     

    Thereafter we can Create our vs Column with below dax Expression


    vs =

    var result =
    DIVIDE('Sample table'[Total],MAX('Sample table'[Custom_Carbon]),0)

    return ROUND(IF(result =
    1,BLANK(),result),2)

     

     

    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

3 Replies

  • SamInogic's avatar
    SamInogic
    Icon for Super User rankSuper User
    3 years ago

    Hi,

     

    As per our understandings you are looking for a column based on some Conditions,
    Here what I have found for Reaching to your Requirement,

     

    Initially we have to Create Calculated column with below Dax Expression,


    Custom_Carbon =

    var Basline =
    IF('Sample table'[Carbon] = "Baseline 19/20",'Sample table'[Total],0)

    return
    Basline

     

    Thereafter we can Create our vs Column with below dax Expression


    vs =

    var result =
    DIVIDE('Sample table'[Total],MAX('Sample table'[Custom_Carbon]),0)

    return ROUND(IF(result =
    1,BLANK(),result),2)

     

     

    Thanks!

    Inogic Professional Service Division

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

    • timEalll's avatar
      timEalll
      Regular Visitor
      3 years ago

      Hi SamInogic what should I do when another Column is added called company as shown below

      When I used your solution it didn't work becasue of the baseline. For company "IGG" has a baseline 19/20 of 1121" and company BPA has a baseline 19/20 of 1000.

      How can I achieve this ensuring that each company should use it baseline for the calculation? as shown above in Vs column