Summing Up Values for top 10 customer and display in CARD

[dinesharivalaga]

Hi All ,

I have a scenario to calculate all Onaccount values for top 10 customers and give the output into a CARD visual .

I have attached the table below which have top 10 customers and Onaccount values as well .

I tried few ways to figure it out but no luck . sum of on account will give huge value (for all customers but i need to sum only for top 10 customers only)

Please help me on this ..

The total Onaccount value should be 3.59 as per top 10 customers .

TIA

[johnmelbourne · Accepted solution]

I think this might be my first response to a Power BI forum question so here goes ....

Filename solution.pbix
Size 20.75 KB
URL https://tmpfiles.org/dl/894730/solution.pbix
Expires at 2023-02-02 07:25 UTC

The approach to take is

1. First create a summary table (using say SUMMARIZE or ADDCOLUMNS) that has the columns you need to work with. 
2. Then filter it somehow (eg: using say FILTER or TOPN) to get the rows you need to work with.
3. The perform some form of aggregation (an X function) on it, like SUMX, MAXX, MINX, AVERAGEX etc

Total On Account = SUM('Table'[On Account])

Top 10 On Account =

SUMX (

    // Get top 10 rows based on the Total On Account field

    TOPN (

        10,

        // Summarize the data in the 'Table' based on Customers and On Account fields

        SUMMARIZE (

            'Table',

            'Table'[Customers],

            'Table'[On Account]

        ),

        // Order the data by Total On Account in descending order

        [Total On Account], DESC

    ),

    // Sum the Total On Account field for the top 10 rows

    [Total On Account]

)

    // Sum the Total On Account field for the top 10 rows

    [Total On Account]

)

---------------------------------

Using Calculate you could write it like this .... (but I prefer the non Calculate way)

Calculate top 10 =

CALCULATE (

    [Total On Account],

    KEEPFILTERS (

        TOPN ( 10, ALL ( 'Table'[Customers] ), [Total On Account], DESC )

    )

)

[dinesharivalaga]

Hi @johnmelbourne  Thanks for your quick response and it works well but .. i used like below ..

total onacc = SUM((O2C_NAR_NAAC_On_Account[On Account]))

Top 10 Onaccount by cus =

SUMX(

TOPN(

10,

SUMMARIZE

(

O2C_NAR_NAAC_On_Account,O2C_NAR_NAAC_On_Account[Customer Name],O2C_NAR_NAAC_On_Account[On Account]),[total onacc],DESC

)

,[total onacc]

)

here the result showing as 2.89M 😞 not 3.59M

DOn't know why ..

Can you clarify more on this ? I did the same as per your formula ..

[johnmelbourne]

It appears you have two brackets surrounding the original measure sum when only one is needed.

Also, if you are getting 2.89, I suspect there is an external filter applying somewhere.  Hover over the card funnel to see what filters are applying on the card visual.

total onacc = SUM((O2C_NAR_NAAC_On_Account[On Account])) IS INCORRECT

total onacc = SUM(O2C_NAR_NAAC_On_Account[On Account])  

Try fix the first simple measure first and see if that impacts things.

Then if you are still getting 2.89 ... which is a weird number to get btw, then try 

Top 10 Onaccount by cus v2 = 

CALCULATE (

    [total onacc],

    KEEPFILTERS (

        TOPN ( 10, ALL ( 'O2C_NAR_NAAC_On_Account'[Customer Name] ), [total onacc], DESC )

    )

)

If that doesn't calculate correctly, then try and identify what it is you underlying dataset is by ...

Create a new table called vTable and have a look at the data it is summing.
Click New Table
vTable = 

TOPN(

10,

SUMMARIZE

(

O2C_NAR_NAAC_On_Account,O2C_NAR_NAAC_On_Account[Customer Name],O2C_NAR_NAAC_On_Account[On Account]),[total onacc],DESC

)

Then go to the table view of your new table and see what the values are in O2C_NAR_NAAC_On_Account[Customer Name],O2C_NAR_NAAC_On_Account[On Account]) and see if they sum to the correct amount.  

[dinesharivalaga]

@johnmelbourne 

Good News is CALCULATE top 10 formula is working fine and result given as expected : 3.59M

Thanks a lot for your help 🙂