Forum Discussion

bogomda's avatar
bogomda
Resolver I
7 years ago
Solved

measure calculation - very odd problem

i wrote a simple measure Managed Services = CALCULATE( SUM(Budget[Amount], Budget[Account Group] = "Managed Services") which internally should translate into:   CALCULATE( ...
Need Help
  • Baskar's avatar
    Baskar
    7 years ago

    bogomda 

     

    Got it the issue, You are 100 % correct it is becasue of the sorting order. 

    Try this, It will help you. 

     

    Managed Services Budget ALL =
    CALCULATE([Total Budget],FILTER(ALL(Budget[Account Group],Budget[Account Group Sort Order]),
    Budget[Account Group]="Managed Services"))
     
bogomda's avatar
bogomda
Resolver I
7 years ago

Baskar's avatar
Baskar
Resident Rockstar
7 years ago

bogomda

 

Can you please share some sample data.

  • bogomda's avatar
    bogomda
    Resolver I
    7 years ago

    Baskar, I investigated a little bit further and found the couse of the problem which will be imporssible to identify without DAX studio.

     

    The trace shows that when "sort by column" feature is used (in my case Budget[Account Group] is sorted by Column Budget[Account Group Sort Order]) and when sorted column is placed on visual, Power BI actually "places" both columns on visual 1) "sorted column" as well as 2) "sort by column". "Sort By Column" is in the visual for sorting purposes but somehow is hidden. Besides sorting, "Sort by column" also creates a side effect - add filter context which I don't see on visual but see in DAX query trace - as a result my measure gets filtered not by Budget[Account Group] but by Budget[Account Group Sort Order] column.

    Knowing this behaviour of "sort by column", I was able to fix my measure by additing additional argument to CALCULATE (see in bold).

     

    Thanks for your time in looking into it.

     

    Managed Services Budget =
    CALCULATE([Total Budget], Budget[Account Group] = "Managed Services",all(Budget[Account Group Sort Order]))

     

    • Baskar's avatar
      Baskar
      Resident Rockstar
      7 years ago

      bogomda 

       

      Got it the issue, You are 100 % correct it is becasue of the sorting order. 

      Try this, It will help you. 

       

      Managed Services Budget ALL =
      CALCULATE([Total Budget],FILTER(ALL(Budget[Account Group],Budget[Account Group Sort Order]),
      Budget[Account Group]="Managed Services"))