HOW to calculate SUB TOTAL in MATRIX in POWER BI

[Anonymous]

Hi All,

Please help !!

I need to add subtotal for each week and Progress Line which will add WTD figure as shown in figure .

[AnkitBI · Accepted solution]

Grt @Anonymous . Please mark it as Solution then.

[v-lili6-msft]

hi, @Anonymous

After my research, If you want to add a custom total row for matrix visual, I'm afraid it couldn't achieve in Power BI for now.

You may try to add a column beside it.

Step1:

You could add a weeknum and yearnum column in power bi desktop or power query. 

Year = YEAR('DataTable'[Date]) 

Week = WEEKNUM('DataTable'[Date])

Note: Is Calenderweek in your screenshot virtual data or custom data? 

Step2:

Then use this formula to add a measure

ProgressTY = 

    CALCULATE (
        SUM ( 'DataTable'[TYSALE] ),
        FILTER (
            ALL ( 'DataTable'[CalenderWeek], 'DataTable'[Date],'DataTable'[Year] ),
            'DataTable'[CalenderWeek] <= MAX ( 'DataTable'[CalenderWeek] )&&'DataTable'[Date]<=MAX('DataTable'[Date])&&'DataTable'[Year]=MAX('DataTable'[Year])
        )
    )

ProgressWTY = 

    CALCULATE (
        SUM ( 'DataTable'[TYSALE] ),
        FILTER (
            ALL ( 'DataTable'[CalenderWeek], 'DataTable'[Date],'DataTable'[Year] ),
            'DataTable'[CalenderWeek] = MAX ( 'DataTable'[CalenderWeek] )&&'DataTable'[Date]<=MAX('DataTable'[Date])&&'DataTable'[Year]=MAX('DataTable'[Year])
        )
    )

Best Regards,

Lin

[AnkitBI]

@Anonymous Please find my solution at PBIX . Might seesms bit lenghty and complex but Hopefully this will be helpful. Basically What I have done is :

1) Transform your data into Tabular format using Power Query as below.

Date	TYSALE	LYSALE	FCTSALE	CalenderWeek
14/07/2018	200	300	200	24
15/07/2018	300	400	250	24
16/07/2018	200	300	200	24
17/07/2018	100	200	200	25
18/07/2018	200	300	100	25
19/07/2018	200	200	300	26
20/07/2018	200	300	200	26

2) Then used a Matrix to show your values. Don't think there is an option to show Progress on Rows but we can show them on Columns. So basically created three measures to show progress using below sample formula.

ProgressTY = 
IF (
    NOT ( ISINSCOPE ( 'DataTable'[Date] ) ),
    CALCULATE (
        SUM ( 'DataTable'[TYSALE] ),
        FILTER (
            ALL ( 'DataTable'[CalenderWeek], 'DataTable'[Date] ),
            'DataTable'[CalenderWeek] <= MAX ( 'DataTable'[CalenderWeek] )
        )
    ),
    BLANK ()
)

3) Output will be like below with progress shown on Columns.

Regards
AJ
Do Like Post if response seems good and Worth liking.
Do Mark as Solution if response resolved your Issue.

[Anonymous]

Hi @AnkitBI

I have converted to power query and run the new measure TYSalesProgress giving me same values as original column.

also ISINSCOPE() not showing in my Power bi Desktop .

Thanks in advance .

[AnkitBI]

@Anonymous IsInScope was released in the December 2018 version of Power BI. 

Regarding Progress in Rows, currently this is not possible in Power BI, as it only allows to add measure in Columns and we can only tinker with default options in Rows.

[Anonymous]

Thanks @AnkitBI and @v-lili6-msft

solutions works for me can add running total for column but not row as you mentined .

Thanks guys !!

[AnkitBI · Accepted solution]

Grt @Anonymous . Please mark it as Solution then.

[Anonymous]

Hi @AnkitBI ,

I want to achive accumlation values as below if you can advise .

Thanks in Advance !!

[AnkitBI · Accepted solution]

Please try below

ProgressTY = 
    CALCULATE (
        SUM ( 'DataTable'[TYSALE] ),
        FILTER (
            ALL ( 'DataTable'[Date],'DataTable'[CalenderWeek] ),
            'DataTable'[Date] <= MAX ( 'DataTable'[Date] )
        )
    )

[Anonymous]

@AnkitBI  Thank you so much  its works !!

[Anonymous]

Thanks @AnkitBI

Can you please tell me how can I transform Data to power query . I am running custom SQL for pulling dataset. 

Business requirement is to show progress in Row and not in column .

Thanks for sharing will help me a lot .

[Anonymous]

HI @AnkitBI,

ISINSCOPE() function not showing in power BI Desktop.

please advise thanks .