How can I create a dynamic sum of the % for the rows in a matrix visual?

Question

It's my first time using this website, and I am a newbie with PowerBi, so apologies in advance if the question isn't clear.

This is a quickly reproduced scenario from the report I am working on: Filtered Picture of the matrix visual

Basically in one of the columns I am using just a simple measure to obtain the CT% of the amount. This one will dynamically change to the correct % as I remove the filters: Picture of matrix visual 2

What I would need is a column on this visual that will sum, row by row, the total % to allow me to identify which and how many invoices will add up to let's say the 10% of the grand total.

The other two columns you can see in the pictures, are DAX columns that I've created like this:

% over month = DIVIDE(('Union table test'[Amount]),CALCULATE(SUM('Union table test'[Amount]),FILTER('Union table test','Union table test'[Date]),DATESBETWEEN('Union table test'[Date],STARTOFMONTH('Union table test'[Date]),EOMONTH('Union table test'[Date],0))),0)

Sum of % over month = CALCULATE(sum('Union table test'[% over month]),FILTER('Union table test','Union table test'[Date]),DATESBETWEEN('Union table test'[Date],STARTOFMONTH('Union table test'[Date]),EOMONTH('Union table test'[Date],0)),FILTER('Union table test','Union table test'[% over month]>=EARLIER('Union table test'[% over month])))

The problem is that they will only show the correct numbers when the appropriate filters have been applied, but what I'd like is something that will always dynamically update depending of the filtered or unfiltered status with the appropriate %.

Any advice on how to do that?

Many thanks!

Answer 1

@Ahr, I have tinkered around with this a bit further.

The best version I have come up with so far performance-wise, is inspired by https://www.daxpatterns.com/abc-classification/

I have updated the PBIX above. PBIX link

The key appears to be to create table variables that can be reused in each row of the table visual. The DAX engine is smart enough not to re-evaluate these tables every time. My original version didn't create a table to use for the cumulative calculation, while this one does (CumulatedAmountByInvoice).

I tested the code with a larger dataset and it was a definite improvement on the original version.

Sum of % V2 =

VAR CurrentInvoice =
    FIRSTNONBLANK (
        TOPN (
            1,
            VALUES ( 'Union table test'[Invoice] ),
            [Amount Sum],
            ASC
        ),
        0
    )
    
VAR TotalAmount =
    CALCULATE (
        [Amount Sum],
        ALLSELECTED ()
    )
VAR AmountByInvoice =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Union table test'[Invoice] ),
            "@InvoiceAmount", [Amount Sum]
        ),
        ALLSELECTED ()
    )
VAR CumulatedAmountByInvoice =
    ADDCOLUMNS (
        AmountByInvoice,
        "@CumulatedAmount",
        VAR  CurrentInvoiceAmount = [@InvoiceAmount]
        VAR  CumulatedInvoice =
            FILTER (
                AmountByInvoice,
                [@InvoiceAmount] >= CurrentInvoiceAmount
            )
        VAR  CumulatedInvoiceAmount =
            SUMX (
                CumulatedInvoice,
                [@InvoiceAmount]
            )
        RETURN
            CumulatedInvoiceAmount
    )
VAR CurrentInvoiceCumulatedAmount =
    SELECTCOLUMNS (
        FILTER (
            CumulatedAmountByInvoice,
            'Union table test'[Invoice] = CurrentInvoice
        ),
        "@CumulatedAmount", [@CumulatedAmount]
    )
RETURN
    DIVIDE ( CurrentInvoiceCumulatedAmount, TotalAmount )

Answer 2

What you are wanting to do is similar to this article (for example): https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

I created a simple PBIX to illustrate, modified a bit compared to the above. PBIX link

I didn't worry about restricting the sum to a single month, but you could alter to do this. You should use a Date table related to your fact table to handle date-related filtering.

The [Sum of %] measure looks like this:

Sum of % = 
VAR AmountPerInvoice =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Union table test'[Invoice] ),
            "@AmountPerInvoice", [Amount Sum]
        ),
        ALLSELECTED ()
    )
VAR CurrentAmount =
    MINX (
        VALUES ( 'Union table test'[Invoice] ),
        [Amount Sum]
    )
VAR TotalAmountSum =
    CALCULATE ( 
        [Amount Sum],
        ALLSELECTED (  )
    )
VAR CumulativeAmountSum =
    SUMX (
        AmountPerInvoice,
        IF (
            [@AmountPerInvoice] >= CurrentAmount,
            [@AmountPerInvoice]
        )
    )
RETURN
    DIVIDE ( CumulativeAmountSum, TotalAmountSum )