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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Pkong001
Regular Visitor

Need Help with Dynamic TOPN Filtering with chart filter chart

Hi Power BI Community,

 

Need Help with Dynamic TOPN in Power BI Visuals!, Sorry if this problem have been resolved in others posts,but I cannot find one.
I'm wrestling with a challenge in my Power BI model and would appreciate any guidance.

 

Overview:

Tables:
Billing (Transaction table)
Product (Dimension table)
Customer (Dimension table)
Date table (for slicers)
Relationships: Established between the tables.


Objective:

Customer Charts: Two visuals:
a. TOPN customers by sales
b. TOPN customers by Sales growth

The number of customers in TOPN should be dynamic (range: 1-20).

 

Product Charts: Two visuals:
a. Sales by Product
b. Sales growth by Product.

These charts should show only Product associated with the selected TOPN customers, without relying on chart interactions for filtering.

 

What I've Done So Far:

I have created a "zTableRange" calculated table for the dynamic TOPN slider. 
Then I create my measures.

 

[Netsales Bill (ACT)] = 

VAR Myresult =CALCULATE( SUM(BillingDocument[NetValue]), BillingDocument[ScenarioCode] = "ACT" ) +0

RETURN

    IF( Myresult = 0, 0, Myresult )

SumFilterByTopN = 

VAR SelectedN= SELECTEDVALUE(zTableRange[RangeValue])

VAR TopCustomersList = TOPN(SelectedN, VALUES(BillingDocument[ParentCustomerCode]), [Netsales Bill (ACT)], DESC)

RETURN

 CALCULATE([Netsales Bill (ACT)],

         FILTER(  BillingDocument , BillingDocument[ParentCustomerCode] IN TopCustomersList ))


My measures [Netsales Bill (ACT)] and [SumFilterByTopN] seem to work as expected when I test it in DAX Studio. However, visuals on Power Bi didn't initially reflect the expected filtering for customers, it display all the customers, even though I've filtered it to show only the TOPN customers using [SumFilterByTopN]. I then created a filter measure [RankByCustomerFilter], which seems to have resolved the issue for customer charts.

This is my Filter Measure.

RankByCustomerFilter = 

VAR RankCust = RANKX(ALL(BillingDocument[ParentCustomerCode]),[Netsales Bill (ACT)],,DESC)

VAR SelectedN= SELECTEDVALUE(zTableRange[RangeValue])

RETURN

    IF(RankCust <= SelectedN,1, 0)

This is how I test my output in DAX Studio



DEFINE

VAR TopCustomersList = TOPN( 2, ALL( BillingDocument[ParentCustomerCode] ), [Netsales Bill (ACT)], DESC )

EVALUATE

TopCustomersList


DEFINE

VAR TopCustomersList = TOPN( 2, ALL( BillingDocument[ParentCustomerCode] ), [Netsales Bill (ACT)], DESC )

EVALUATE

FILTER( BillingDocument, BillingDocument[ParentCustomerCode] IN TopCustomersList)


DEFINE

VAR TopCustomersList = TOPN( 2, ALL( BillingDocument[ParentCustomerCode] ), [Netsales Bill (ACT)], DESC )

VAR Mynewtable = FILTER( BillingDocument, BillingDocument[ParentCustomerCode] IN TopCustomersList )

EVALUATE

SUMMARIZE(MynewTable,

[ParentCustomerCode],

"NetMeasure", [Netsales Bill (ACT)])

 

Current Problems/Challenge:
The Product Charts aren't displaying as desired. It display all Products, rather than just those associated with the selected TOPN customers. The total sales, as calculated by the Product chart, appears to reflect the correct value for the TOPN customers. However, when I manually sum the sales displayed on the chart, the numbers don't match.

 

Expected Result:
For product charts to show only products and sales associacted with selected customers and respond to other slicers dynamically.


PBIX File: Sorry for the file size (10MB).

https://drive.google.com/file/d/1dcALx2RAlB4iKeAwHO3bjPvpDdG-gxZ-/view?usp=drive_link

 

Screenshots:

PIC1.pngPIC2.png

 

Any guidance or solutions would be highly appreciated. Thank you in advance!

1 REPLY 1
lbendlin
Super User
Super User

It is recommended to use ALLSELECTED() rather than ALL().  You can also consider using the built in TOP N filter in the filter pane - that requires no code.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.