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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sirius
Frequent Visitor

sumx not affected by slicer in different table (creating pareto chart which is affected by slicer)

Hi,

 

In this simplified example, I am trying to create a pareto chart of Suppliers which should be sliceable.

 

The setup is this: Spend data is in an item table, which the type slicer operates on. The itemtable is summarized in a derived supplier table and then graphed. The supplier table and the item table is related through supplierID, with cross filtering direction set to both.

 

Capture.PNG

 

I opted to create the supplier table with:

SupplierTable = VALUES(ItemTable[SupplierID])

And then adding a Turnover column with:

 

Turnover = SUMX(FILTER(ALLSELECTED('ItemTable'); ItemTable[SupplierID]=SupplierTable[SupplierID]); ItemTable[Turnover])

a Ranking column:

Ranking = RANKX(FILTER('SupplierTable';ISBLANK(SupplierTable[Turnover])=FALSE());SupplierTable[Turnover])

a Supplier name column:

SupplierName = "Supplier "&SupplierTable[SupplierID]

a Cumulative Total measure:

Cumulative Total = CALCULATE(
    SUM(SupplierTable[Turnover]);
    FILTER(ALLSELECTED(SupplierTable);
        SupplierTable[Ranking] <= MAX(SupplierTable[Ranking])
    ))

a Total Turnover measure:

Total Turnover = CALCULATE(SUM(ItemTable[Turnover]);ALL(SupplierTable))

And finally a Cumulative Percent measure:

Cumulative Percent = [Cumulative Total] / [Total Turnover]

Doing the Pareto chart without slicing works. But I have obviously made a mistake in how the Turnover column is calculated. When applying the slicer to the Item table, this filtering does not propegate to the supplierTable[Turnover] column:

Capture2.PNG

 

 

 

How do I make the slicer work on the Pareto chart? There is only one slicer in this example, but in my report there are several and the user should be able to slice on new columns too, so the solution needs to handle this.

 

Hope you can help!

 

6 REPLIES 6
Elaine_H
Frequent Visitor

I did something similar recently, I tried to use your table and column names but may have them slightly wrong.

 

Create your Supplier Table in the following way:

 

 

SupplierTable2 = SUMMARIZE(ItemTable,ItemTable[SupplierID],"SupplierName","Supplier "&ItemTable[SupplierID],"Turnover",sum(ItemTable[Turnover])) 

 

Create both a Cumulative Total Measure and Column:

 

 

Cumulative Total Measure = CALCULATE (
    SUM (SupplierTable2[Turnover] ),
    FILTER (
        ALL ( SupplierTable2 ),
        SupplierTable2[Ranking] <= MAX (SupplierTable2[Ranking])
    )
)

'Add as Column:
Cumulative Total = CALCULATE(
    SUM(SupplierTable2[Turnover]),
    FILTER(ALLSELECTED(SupplierTable2),
        SupplierTable2[Ranking] <= EARLIER(SupplierTable2[Ranking])
    ))

 

 

 

To get to the Cumulative Percent, I first created a Turnover % of the total for each supplier:

 

 

Turnover % of Total = 
DIVIDE (
SUM('SupplierTable2'[Turnover]),
CALCULATE ( 'SupplierTable2'[Cumulative Total Measure], ALL ( 'SupplierTable2') ),
BLANK ()
)

 

 

Then use that to get the Cumulative Percent using ALLSELECTED:

 

 

 

Cumulative Percent = CALCULATE(
[Turnover % of Total],
FILTER (
ALLSELECTED( 'SupplierTable2' ),
'SupplierTable2'[Cumulative Total] <= MAX ( 'SupplierTable2'[Cumulative Total] )
)
)

 

 

Hi Elaine_H,

 

I tried your solution, but it still doesn't seem to respond to the slicer?

 

Or am i missing something?

 

 

Capture3.PNG

 

 

 

 

Sorry, I forgot to mention that you need to create a relationship between the new SupplierTable and the Item table on SupplierID (filter both directions).

I believe i have done that:

 

 

 

Capture4.PNG

 

 

But still same results...

 

 

 

Hi @sirius,

 

I find that the chart contains Cumulative Percent = [Cumulative Total] / [Total Turnover] value. Both [Cumulative Total] and [Total Turnover] has AllSELECTED() and ALL() functions. The [Total Turnover] will return all SUM(ItemTable[Turnover]) ignore any filter include slicer. While the [Cumulative Total] can be filtered by the slicer but the chart context will be ignored. I think that should be the issue.

 

Please review the function of ALLSELECTED() and ALL() function to check if chart display the expected results based on measures.

 

By the way, the Format -> Edit Interactions feature in desktop will determine if visuals filter or not. You can take a look at this.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the input @v-qiuyu-msft and @Elaine_H,

 

I "fixed" the total turnover by using an allexcept,

 

 

Total Turnover = CALCULATE(SUM(ItemTable[Turnover]); ALLEXCEPT(ItemTable; ItemTable[Type]))

 

 

However, I definitely lack an understanding of the workings of the dax-expressions I use. The offending Turnover column is still misbehaving:

 

 

Turnover = SUMX(FILTER(ItemTable; ItemTable[SupplierID]=SupplierTable[SupplierID]); [Turnover])

 

 

My current hypothesis is that the filter function internally translates to dax code that uses an ALL. But this only based on http://www.sqlbi.com/articles/filter-arguments-in-calculate/, where it happens in a calculate expression.

 

Don't know how to proceed from here..

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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