cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## HOW to calculate SUB TOTAL in MATRIX in POWER BI

Hi All,

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Established Member

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

Grt @dalethakar . Please mark it as Solution then.

Established Member

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

```ProgressTY =
CALCULATE (
SUM ( 'DataTable'[TYSALE] ),
FILTER (
ALL ( 'DataTable'[Date],'DataTable'[CalenderWeek] ),
'DataTable'[Date] <= MAX ( 'DataTable'[Date] )
)
)```
11 REPLIES 11
Established Member

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

@dalethakar 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.

Frequent Visitor

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

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 .

Frequent Visitor

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

HI @AnkitBI,

ISINSCOPE() function not showing in power BI Desktop.

Frequent Visitor

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

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 .

Community Support Team

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

hi, @dalethakar

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Established Member

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

@dalethakar 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.

Frequent Visitor

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

Thanks @AnkitBI and @v-lili6-msft

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

Thanks guys !!

Established Member

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

Grt @dalethakar . Please mark it as Solution then.

Frequent Visitor

## Re: HOW to calculate SUB TOTAL in MATRIX in POWER BI

Hi @AnkitBI ,

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 208 members 2,311 guests
Recent signins: