cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dalethakar Frequent Visitor
Frequent Visitor

HOW to calculate SUB TOTAL in MATRIX in POWER BI

Hi All,

 

 

 

Please help !!Capture.PNG

 

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
AnkitBI Established Member
Established Member

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

Grt @dalethakar . Please mark it as Solution then.

View solution in original post

AnkitBI Established Member
Established Member

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

Please try below

 

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

View solution in original post

11 REPLIES 11
AnkitBI Established Member
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.

 

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

dalethakar Frequent Visitor
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 .

dalethakar Frequent Visitor
Frequent Visitor

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

HI @AnkitBI,

 

ISINSCOPE() function not showing in power BI Desktop.

 

please advise thanks .

dalethakar Frequent Visitor
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 .

 

Thanks in advance .

Community Support Team
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? 

 

2.JPG

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])
        )
    )

3.JPG

 

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.
AnkitBI Established Member
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.

dalethakar Frequent Visitor
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 !!

AnkitBI Established Member
Established Member

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

Grt @dalethakar . Please mark it as Solution then.

View solution in original post

dalethakar Frequent Visitor
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 .

 

 

Capture.PNG

 

Thanks in Advance !!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 208 members 2,311 guests
Please welcome our newest community members: