none
Works in PowerBI but not in PowerPivot - top 80% of revenue RRS feed

  • Question

  • I want to create a measure that counts the rows that make up top 80% of the total revenue. So I have a very simple table (below).

    Cust rev
    a 10
    b 20
    c 30
    d 40
    e 50

    So the following measure should return 3 (the top 3 are e+d+c = 120, total is 150, 120/150=80%). It works correctly in PowerBI, but in PowerPivot, I always get (blank).

    When looking into it, it seems that in PowerPivot, vRunningPercentage, within vTable1, always evaluate to 1, across every row. The issue seems to be related to the EARLIER statement within the SUMX.

    Any idea why that'd work in PowerBI but not in PowerPivot? I thought the two were interchangeable.


    Revenue:=SUM([rev])
    TopN Customers = VAR totalSales = [Revenue]
    VAR vTable = SUMMARIZE(Table1,
    		Table1[Cust],
    		"vSales",[Revenue],
    		"vRankingOrg",RANKX(ALLSELECTED(Table1),[Revenue],,DESC),
            "vPercentage", [Revenue]/totalSales
    		)
    VAR vTable1=ADDCOLUMNS(vTable,
    	"vRunningPercentage",SUMX(FILTER(vTable, [vRankingOrg]<=earlier([vRankingOrg])),[vPercentage])
    	)
    RETURN
    COUNTROWS(FILTER(vTable1,[vRunningPercentage]<=0.8))



    • Edited by riccD Tuesday, October 1, 2019 10:05 PM
    Tuesday, October 1, 2019 5:46 PM

Answers

  • I don't know why it is working in one and not the other.  Here is how I would write this formula.  It works in Power Pivot.

    =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                Data,
                "Cumulative",
                VAR thisCust = CALCULATE ( MAX ( Data[Rev] ) )
                VAR AllCust = SUM ( Data[Rev] )
                RETURN
                    CALCULATE ( SUM ( Data[Rev] ), FILTER ( ALL ( Data ), Data[Rev] >= thisCust ) ) / AllCust
            ),
            [Cumulative] <= 0.8
        )
    )


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Thursday, October 3, 2019 9:49 AM
    Answerer

All replies

  • I don't know why it is working in one and not the other.  Here is how I would write this formula.  It works in Power Pivot.

    =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                Data,
                "Cumulative",
                VAR thisCust = CALCULATE ( MAX ( Data[Rev] ) )
                VAR AllCust = SUM ( Data[Rev] )
                RETURN
                    CALCULATE ( SUM ( Data[Rev] ), FILTER ( ALL ( Data ), Data[Rev] >= thisCust ) ) / AllCust
            ),
            [Cumulative] <= 0.8
        )
    )


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Thursday, October 3, 2019 9:49 AM
    Answerer
  • Works and is much cleaner. Still don't know why it works in one but not the other, but it works!
    Thursday, October 3, 2019 6:00 PM