Forum Discussion

yasemsem's avatar
yasemsem
Frequent Visitor
2 years ago

How to filter when slicer table does not have direct relationship with the to be filtered table

I want to get % of sales(AE) with Pipeline and booking in the pie chart I created new column in the Sales table with the below code   Pie Chart: The above code works but it doesn't work when...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi yasemsem 

 

In response to your question, here is some advice I would give:

 

First, make sure that both the Sales table and the table containing the SFDC Quarterly information have a date column that can be used to create an indirect relationship through the Date table.

 

This Date table should contain all dates, quarters, etc. and link to your Sales and SFDC Quarterly tables.

 

You can create a metric that dynamically calculates the percentage of sales (AE) based on the selected quarter.

 

This metric can be used to select the current quarter in the slicer and then calculate sales for that quarter. Example:

 

SalesPercentage = 
VAR SelectedQuarter = SELECTEDVALUE('SFDCQuarterTable'[Quarter])
RETURN
CALCULATE(
    SUM('SalesTable'[AE]),
    FILTER(
        'SalesTable',
        'SalesTable'[Quarter] = SelectedQuarter
    )
) / CALCULATE(SUM('SalesTable'[AE]), ALL('SalesTable'))

 

Regards,

Nono Chen

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