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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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