Forum Discussion

StevenUser123's avatar
StevenUser123
Frequent Visitor
3 years ago

Filtering A Visual Without Applying Filters

Hello, I am trying to create a Comparison View between two sides, and am having trouble getting a common table to reference each side of the comparison. As you can see below, I have selected a total...
Need Help
PawelWrona's avatar
PawelWrona
Resolver II
3 years ago

Can you share calculation for "Current Sales Table 1"? Could you somehow mask the data and share pbix file?

StevenUser123's avatar
StevenUser123
Frequent Visitor
3 years ago

PawelWrona My formula is currently:

Current Sales Table 1 = CALCULATE('FACT_SALES'[Current Sales], FILTER('DIM_SKU',DIM_SKU[Product name]= SELECTEDVALUE('Comparison_Table 1'[Product name])))

And [Current Sales] from Fact_Sales is:
Current Sales = VAR EndDate=EOMONTH(DATE([EndYear],[EndMonth],1),0)
VAR StartDate=DATE([BeginningYear],[BeginningMonth],1)
RETURN
CALCULATE([Net Sales], DATESBETWEEN(DIM_DATE[Date],StartDate,EndDate))
 
I will work on removing data for a PBI file to share, but let me know if you may have an idea based on that formula. Thanks!
  • PawelWrona's avatar
    PawelWrona
    Resolver II
    3 years ago

    Could you provide the calculation for [Net Sales] as well? Still, pbix file would be the best option here.

     

    And small off-topic here. Try not to use FILTER function within CALCULATE, especially when you pass entire table as an argument, it's a performance killer.

    • StevenUser123's avatar
      StevenUser123
      Frequent Visitor
      3 years ago

      PawelWrona Net Sales is:

      Net Sales = CALCULATE(SUM(FACT_SALES[Value]),FACT_SALES[Attribute]="Net")
       
      And understandable if you may need the model to troubleshoot this. Appreciate the headsup on the Filter function.
       
      Thank you