Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
yasemsem
Frequent Visitor

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

 

yasemsem_0-1715845837736.png

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

 

yasemsem_0-1715846214454.png

 

 

 

2 REPLIES 2
yasemsem
Frequent Visitor

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

v-nuoc-msft
Community Support
Community Support

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.