cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Calculate Quarters and add them to matrix

Hi all,

 

I have made the matrix below but I not sure on how can I fill some columns e.g. quarters. Probably I will need measures but again not sure how to use them or how to put them in the vizualization tab in order to present them correctly.

 

This is my final output, the matrix that I want to create in my dashboard

 

final report.png

 

The tables that I use is the ones here and another one that contains only the Accounts - Subaccounts - Subsubaccounts etc.

 

DimDate1.pngDimHeaders.pngFactTableps.jpg

The relations between the tables is here

Relations new.png

My visuals options is this

visual.png

 

I created the measure which I think is the correct one (if not please tell me)

 

Q1 2020 =
  CALCULATE(
            SUM(FactTable[Amount]),
            GROUPBY('FactTable', [Account]),
            DimDate[Year] = 2020,
            DimDate[Quarter] = 1)
 
how can I "join" it to the matrix on my dashboard?
1 ACCEPTED SOLUTION
Memorable Member
Memorable Member

@ft41 Why don't you try the below option. You will need to create a quarter column (Quarter = Append1[Date].[Quarter] )

and put it between year and month. You can modify the column header from the format option to show it as quarter total.

 

negi007_1-1603113922572.png

 

 

View solution in original post

2 REPLIES 2
Memorable Member
Memorable Member

@ft41 Why don't you try the below option. You will need to create a quarter column (Quarter = Append1[Date].[Quarter] )

and put it between year and month. You can modify the column header from the format option to show it as quarter total.

 

negi007_1-1603113922572.png

 

 

View solution in original post

Super User IV
Super User IV

@ft41 , As you have date you can use datesQtd and date table for this and prior qtr

 

example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

 

The disply you have provided in the first image in not possible.

refer blog from Greg and Paul for Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

 

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors