Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

DAX Virtual row context?

Hi Guys,

I Have calulations around search volume that works well when I have rows in the context
i.e To Calulate Search Volume of the Label I Divide search volume by Search Volume (all keyword/brands/subcategories)
see below working fine


DAX:

Code to calulate search volume label:

Search Volume (Labels) = CALCULATE(
    SUM( 'Search Volume'[Search Volume] ) ,
    ALL(Keyword[Subcategory] ),
    ALL('Product'[Brand] ),
    ALL('Keyword'[keyword] )    
)


Keyword level calcs

 


I was wondering if there is a way to retain the same values when I remove labels from the visual? - for example always pads should return 32,995 (word always has one label value)
after labels are removed I have wrong totals

 

I was trying summarize/addcolumns/values etc but never was able to get to the correct number, any ideas?

Many Thanks

Need Help
Tips and Tricks

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Anonymous 

    Please try

    Search Volume (Labels) 2 =
SUMX ( VALUES ( 'Table'[Label] ), [Search Volume (Labels)] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Not working, sumx + values creates virtual Table on the list of values and then sum all 

      Search Volume (Labels) values, this returns total, 

      I Want to simulate below
      keyword, label ,search volume
      kw 1, branded,  100
      kw 2,  branded, 50
      kw 3,  non branded, 100
       
      New Measure shoud return 200 for Branded kw and 50 for non branded, (when Label is not part of the visual)
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thank you tamerj1 

        Hi, Anonymous 

        Maybe you can try using the summarize function to achieve something similar. First of all, you need to implement your summation in the summary, such as the sum of labels:

        Since there is no label in your visual, we need to use your existing label to get this value, such as keyword:

        Here are the results:

        In the analog data you provided, branded is 150.How did you calculate that it was 200? The above is how to keep the current calculation in the visual if there is no corresponding label. You can adjust it appropriately to suit your actual situation.

         

         

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.