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.
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:
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!
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 =
CALCULATE ( 'SupplierTable2'[Cumulative Total Measure], ALL ( 'SupplierTable2') ),
Then use that to get the Cumulative Percent using ALLSELECTED:
Cumulative Percent = CALCULATE(
[Turnover % of Total],
ALLSELECTED( 'SupplierTable2' ),
'SupplierTable2'[Cumulative Total] <= MAX ( 'SupplierTable2'[Cumulative Total] )
I tried your solution, but it still doesn't seem to respond to the slicer?
Or am i missing something?
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 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.
By the way, the Format -> Edit Interactions feature in desktop will determine if visuals filter or not. You can take a look at this.
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..