Forum Discussion

craigmon's avatar
craigmon
Helper I
11 months ago
Solved

Help with complex Measures and Calculated columns

Hi Guys,   I'm trying to achive the same as the attachment which in excel, but in Power BI.  I'm really struggling to get these to duplicate, esepcially getting a total of the Negative Values.  Can...
Need Help
  • raji_n's avatar
    raji_n
    11 months ago

    Hi craigmon 

    Please use these measures,
    1. 

    Measure = sumx(SUMMARIZE(Dealbook,Dealbook[Location1],Dealbook[Sales Exec],"x",[01 Negative Diff Only Measure]),[x])

    2. 

    01 Neg Diff Only Measure New = if([01 PPU Diff  Measure]<0,[01 PPU Diff  Measure],if(HASONEVALUE(Dealbook[Location1]),[Measure])) --- add this to your visual to get the values
     
    Please mark this as solution if it resolves your problem
    Thanks,
    Rajashri N
v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
11 months ago

Hi craigmon,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to srlabhe, raji_n, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

 

craigmon's avatar
craigmon
Helper I
11 months ago

Many thanks for the reply, really appreciate you looking into this.  However, its not quite what I was looking for.  I've managed to create all of the measure in my pbix example below, but for some reason the 01 Negative Diff Only Measure does not show a total, it only shows 0.  I need this to be the same as K14 in the example above.  Many thanks

 

https://drive.google.com/drive/folders/1XxniIRgYwkogkolvIIDNvkw-1zpZxx4Z 

  • raji_n's avatar
    raji_n
    Resolver II
    11 months ago

    Hi craigmon 

    Please use these measures,
    1. 

    Measure = sumx(SUMMARIZE(Dealbook,Dealbook[Location1],Dealbook[Sales Exec],"x",[01 Negative Diff Only Measure]),[x])

    2. 

    01 Neg Diff Only Measure New = if([01 PPU Diff  Measure]<0,[01 PPU Diff  Measure],if(HASONEVALUE(Dealbook[Location1]),[Measure])) --- add this to your visual to get the values
     
    Please mark this as solution if it resolves your problem
    Thanks,
    Rajashri N
    • craigmon's avatar
      craigmon
      Helper I
      11 months ago

      this worked perfectly, many thanks for your help

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    11 months ago

    Hi craigmon,

    Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario again with your .pbix file, done some changes, and it worked on my end.


    I am also including .pbix file for your better understanding, please have a look into it:

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Fabric Community Forum.

     

    • craigmon's avatar
      craigmon
      Helper I
      11 months ago

      Thanks for this but the 01 Negative Diff Only Measure show different values to the 01 PPU Diff Measure? The negative values should be the same and any postive should show as 0.   They should be the same as per my orignal pbix file but with the correct total at the bottom rather than 0.  My original 01 Negative Diff Only Measure works as it should at row level, but as you can see the total is not correct.  So I just need the 01 Negative Diff Only Measure to show the correct total not zero.  Thanks