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 I filter using the Quarter. I want it to be dynamic depending on the quarter I am choosing to reflect the sales in this quarter

 

The issue is that SFDC quarter doesn't have a relationship with Sales , however, I believe the modeling is correct and what I need to change is in the calculated column to add something like selectedvalue, but I cannot get it to work

 

 

 

 

Need Help

2 Replies

  • 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.

  • yasemsem's avatar
    yasemsem
    Frequent Visitor
    2 years ago

    Thanks for your response


    The sales table actually is a dimension table with the names of all sales reps

    I have a date table connected to the table of "bookings and pipeline" ( fact table ), the issue is that I cannot connect it to the sales table because it shouldn't have a date as it has only the information of the sales rep

     

    "Sales Rep" table is called in the above screenshot = "CEEMETA SFDC Sales Roster"

     

    I used this ""CEEMETA SFDC Sales Roster"" table to create a calculated column and check the bookings and pipeline with the filters shown in the screenshot of every sales rep so I would be able to create a % of who did have booking and pipeline and who did not

     

    There could be a better way to do it, but it dd not occur to me..