Forum Discussion

FelipeGualberto's avatar
FelipeGualberto
Frequent Visitor
8 years ago
Solved

Filter and divide instead filtering twice with CALCULATE

Hello, I have the measure below and it works fine:

 

Televisions Price per Unit = CALCULATE([Revenue]; DB[Product] = "Television") / CALCULATE([Amount]; DB[Product] = "Television")

 

I wonder if it is possible, just for the sake of my perfeccionism, to filter first the DB table only the rows containing Television and then sum each columns' values and do the division operation. The fact that I must explicitly express the filter context DB[Product] = "Television" bothers me and there may be a better and elegant way.

Also: without breaking the storage engine security.

 

Thanks!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    So something like:

     

    Televisions Price per Unit = 
VAR __tmpTable = FILTER('DB',[Product]="Television")
RETURN 
DIVIDE(
    SUMX(__tmpTable,[Revenue]),
    SUMX(__tmpTable,[Amount]),
    0
)

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    So something like:

     

    Televisions Price per Unit = 
VAR __tmpTable = FILTER('DB',[Product]="Television")
RETURN 
DIVIDE(
    SUMX(__tmpTable,[Revenue]),
    SUMX(__tmpTable,[Amount]),
    0
)
    • FelipeGualberto's avatar
      FelipeGualberto
      Frequent Visitor
      8 years ago

      Thanks, I wonder if it is less efficient than using CALCULATE. I will test it later in DAX Studio.

       

      Have a good day.