Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Please help !!
I need to add subtotal for each week and Progress Line which will add WTD figure as shown in figure .
Solved! Go to Solution.
Grt @Anonymous . Please mark it as Solution then.
Please try below
ProgressTY = CALCULATE ( SUM ( 'DataTable'[TYSALE] ), FILTER ( ALL ( 'DataTable'[Date],'DataTable'[CalenderWeek] ), 'DataTable'[Date] <= MAX ( 'DataTable'[Date] ) ) )
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
@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.
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 .
@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.
Thanks @AnkitBI and @v-lili6-msft
solutions works for me can add running total for column but not row as you mentined .
Thanks guys !!
Grt @Anonymous . Please mark it as Solution then.
Please try below
ProgressTY = CALCULATE ( SUM ( 'DataTable'[TYSALE] ), FILTER ( ALL ( 'DataTable'[Date],'DataTable'[CalenderWeek] ), 'DataTable'[Date] <= MAX ( 'DataTable'[Date] ) ) )
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 .