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

Rolling 4 quarters is not breaking down country wise when adding country field into the table visual

Hi, 

 

I have a fact table called "Due-OnTime".

- The fact table contains Year and quarter wise count of "Due" recods, "Ontime" records, "Country" Column and "Business Area" Column

and I have a Dim table called "Master country"

- The Dim table contains, "Region", "Zone" and "Country"

 

(Both tables are linked using  "Country" Filed)

 

I calculated the rolling 4 quarters using quarter rank 

Last 4 Qtr Due = CALCULATE(sum('Due-OnTime'[# of DUE in the Quater]), FILTER(ALL('Due-OnTime'),'Due-OnTime'[Qtr Rank]>=max('Due-OnTime'[Qtr Rank])-3 && 'Due-OnTime'[Qtr Rank]<=max('Due-OnTime'[Qtr Rank])))

 

Last 4 Qtr Ontime = CALCULATE(sum('Due-OnTime'[# of Ontime in the Quater]), FILTER(ALL('Due-OnTime'),'Due-OnTime'[Qtr Rank]>=max('Due-OnTime'[Qtr Rank])-3 && 'Due-OnTime'[Qtr Rank]<=max('Due-OnTime'[Qtr Rank])))

 

New Divide= Last 4 Qtr Ontime/Last 4 Qtr Due

 

and trying to plot the visual, when i am not taking country column it is working fine.

Kasireddyvina_2-1615213626404.png

 

when I take the "country Name" column from Dim table (Master country) it is working as expected.

 

Kasireddyvina_0-1615214267823.png

but when I take the "country" column from fact table (Due-OnTime), the values are not getting breakdown country wise.

Kasireddyvina_1-1615214282131.png

 

The reason I wnat to take country column from fact table (Due-OnTime) is, I dont have "Business area" column in Dim table (Master country)

as per the requirement i have to use below slicers and slice the data.

 

Kasireddyvina_0-1615213475662.png

Kasireddyvina_1-1615213513059.png

 

Please help me.

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @Kasireddyvina ,

 

This may be due to the filtering direction of the table relationship. Try to create a measure and apply it to the visual levle filter.

measure = if(max('dim'[country]) in values('fact'[country]),1,0)

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @Kasireddyvina ,

 

This may be due to the filtering direction of the table relationship. Try to create a measure and apply it to the visual levle filter.

measure = if(max('dim'[country]) in values('fact'[country]),1,0)

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@Kasireddyvina , This case for better result, You need to have Qtr, year, Qtr Year and Rank all in a separate table

 

I called it Date, but is can any table, but not part fact table

 

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0



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!

Hi @amitchandak , When I am using separate dim table for year quarter I'm getting unwanted records

 

Ex- in my fact table I have data for 5 Quarters (2020-Q1 to 2021-Q1), but when I'm relateing the fact and dim table using year-qtr column and ploting the visual, I'm getting unwated quarters which i dont have in fact table.  ex:- 2021-Q2, 2021-Q3 and 2021-Q4 

Kasireddyvina_0-1615267301881.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors