marhyno
marhyno

Reputation: 699

Ignore one of the filters PowerBI sends to SSAS

I need to calculate pareto but in graph we can only see top 10 items but pareto should be calculated from all items in the selected date period. So e.g. if I have date 1.10.2021 and I have 20 values for that date. I can display only 10 in powerbi, how can I ignore filter for TOPN10 in dax and calculate pareto from 20 values (in graph it wont show 100 percent for pareto calculation)

I am posting filters from PowerBI

DEFINE
VAR __DS0FilterTable = 
    TREATAS({FALSE,
      BLANK()}, 'Breakdown'[Less then 30 sec])

  VAR __DS0FilterTable2 = 
    TREATAS({TRUE}, 'Breakdown'[IsReportedInterval])

  VAR __DS0FilterTable3 = 
    TREATAS({"Reported"}, 'Reported Filter'[Reported])

  VAR __DS0FilterTable4 = 
    TREATAS({DATE(2021, 10, 1)}, 'Date'[Date])

  VAR __DS0FilterTable5 = 
    TREATAS({"Morning"}, 'Shift'[Shift Name])

  VAR __SQDS0BodyLimited = 
    TOPN(10, __SQDS0Core, [Breakdown__min_], 0)

Here is Pareto calculation - If I use ALLSELECTED pareto is calculated from rows filtered by powerbi filters, when I use ALL it will remove all filters which is not correct because I would get sum of all rows excluding date filter. Any ideas ?

Pareto Breakdown Description:=
VAR TotalQuantity =
    CALCULATE ( SUM ( Breakdown[DurationSeconds] ), ALLSELECTED( Breakdown ) )
VAR AllBreakdowns =
    SUM ( Breakdown[DurationSeconds] )
VAR SummarizedTable =
    SUMMARIZE (
        ALLSELECTED ( Breakdown ),
        'Breakdown'[Description SK],
        "Amount", SUM ( Breakdown[DurationSeconds] )
    )
VAR CumulativeSum =
    SUMX ( FILTER ( SummarizedTable, [Amount] >= AllBreakdowns ), [Amount] )
RETURN
   DIVIDE( CumulativeSum, TotalQuantity )

Upvotes: 0

Views: 163

Answers (1)

marhyno
marhyno

Reputation: 699

It will sound maybe little bit silly but I found a workaround - so I removed the filter to show only top 10 rows and within the graph I changed minimum category width which at the end shows only 10 bars (10 are hidden)

enter image description here

Upvotes: 0

Related Questions