Graph Sorting as per Fiscal year; Expansion & contrast - Graph as per FY year, quarter, month

[Anonymous]

Dear Team,

Self had created new column as per Indian Fiscal year - months (Apr to Mar) & also added Fiscal year, half year & quarter. 

Ex: Please find below data

Invoice Date-----FY Month ----FY Month Desc---FY Quarter...FY Half year....FY--Qty

10/04/2021 ----  1 --------Apr-----Q1---- H1....FY22 ---100

10/05/2021 ----  2 --------May-----Q1---- H1....FY22---110

10/06/2021 ----  3 --------Jun-----Q1---- H1....FY22---90

10/07/2021 ----  4 --------Jul-----Q2---- H1....FY22---150

10/08/2021 ----  5 --------Aug-----Q2---- H1....FY22---120

10/09/2021 ----  6 --------Sep-----Q2---- H1....FY22---140

10/10/2021 ----  7 --------Oct-----Q3---- H2....FY22---160

10/11/2021 ----  8 --------Nov-----Q3---- H2....FY22---180

10/04/2020 ----  1 --------Apr-----Q1---- H1....FY21 ---70

10/05/2020 ----  2 --------May-----Q1---- H1....FY21---110

10/06/2020 ----  3 --------Jun-----Q1---- H1....FY21---90

10/07/2020 ----  4 --------Jul-----Q2---- H1....FY21---160

10/08/2020 ----  5 --------Aug-----Q2---- H1....FY21---100

10/09/2020 ----  6 --------Sep-----Q2---- H1....FY21---110

10/10/2020 ----  7 --------Oct-----Q3---- H2....FY21---150

10/11/2020 ----  8 --------Nov-----Q3---- H2....FY21---80

10/12/2020 ----  9 --------Dec-----Q3---- H2....FY21---120

10/01/2021 ----  10 --------Jan-----Q4---- H2....FY21---110

10/02/2021 ----  11 --------Feb-----Q4---- H2....FY21---80

10/03/2021 ----  12 --------Mar-----Q4---- H2....FY21---90

10/04/2019 ----  1 --------Apr-----Q1---- H1....FY20 ---80

10/05/2019 ----  2 --------May-----Q1---- H1....FY20---100

10/06/2019 ----  3 --------Jun-----Q1---- H1....FY20---90

10/07/2019 ----  4 --------Jul-----Q2---- H1....FY20---110

10/08/2019 ----  5 --------Aug-----Q2---- H1....FY20---100

10/09/2019 ----  6 --------Sep-----Q2---- H1....FY20---110

10/10/2019 ----  7 --------Oct-----Q3---- H2....FY20---130

10/11/2019 ----  8 --------Nov-----Q3---- H2....FY20---90

10/12/2019 ----  9 --------Dec-----Q3---- H2....FY20---110

10/01/2020 ----  10 --------Jan-----Q4---- H2....FY20---100

10/02/2020 ----  11 --------Feb-----Q4---- H2....FY20---110

10/03/2020 ----  12 --------Mar-----Q4---- H2....FY20---110

Doubt 1:

Need Graph sorting as per financial year & month & quarter as per fiscal year

Qty should be average for

quarter (qty/3)

half year(qty/6)

year (qty/6)

& not sum

y-axis - Qty

x-axis - Default should show as per FY (Financial year)

If user clicks on specific financial year then should expand has H1 & H2 for that specific financial year.

Once user click on half year - should expand quarter wise

Once user click on quarter - should expand month wise

Note: Qty should be average

Example for X axis expansion

                                              Jul    Aug    Sept       Oct  Nov

            Q1    Q2         Q1      Q2                        Q2

            H1           H2  H1                                   H2

F20      FY21               FY22

Expansion in graph should happen only if click on Year/Half year/ Quarter till month

Also post clicking expansion should reduce to quarter/half/year.

Similar to Pivot.

Doubt 2:

Also confirm how to create table....NOTE: QTY SHOULD BE AVERAGE for quarter/half year/year.

                                              Jul    Aug    Sept       Oct  Nov

            Q1    Q2         Q1      Q2                        Q2

            H1           H2  H1                                   H2

F20      FY21               FY22

Please help in resolving.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

1. The extension of the quarter month of the fiscal year can be realized through the matrix view.

Fiscal year = RIGHT([FY Half year],4)

2. For qty around be average for quarter / half year / year, I created three measures to calculate.

AVERAGE for quarter = 
CALCULATE (
    AVERAGE ( 'Table'[Qty] ),
    FILTER ( ALL ( 'Table' ), [FY Quarter] = MAX ( 'Table'[FY Quarter] ) )
)

AVERAGE for half year = 
CALCULATE (
    AVERAGE ( 'Table'[Qty] ),
    FILTER ( ALL ( 'Table' ), [FY Half year] = MAX ( 'Table'[FY Half year] ) )
)

AVERAGE for year = 
CALCULATE (
    AVERAGE ( 'Table'[Qty] ),
    FILTER ( ALL ( 'Table' ), [Fiscal year] = MAX ( 'Table'[Fiscal year] ) )
)

If the method I provided above can't solve your problem, what's your expected result? Can you use pictures to show your expected results? I hope I can help you and look forward to your reply.

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Dear Team,

Regret for delay in my response.

I want to populate Month Wise Data & Quartely data (in TPM) - 3 months qty/3.

Then apply graph for entire data set & filter quarters or months as required. 

Self not able to attach excel sheet or take pic of graph

I had build seperate report in excel using sumifs & applied graphs. How to do similarly in power bi.

Also confirm how build report with sumifs or other formulas in one sheet & apply graphs for same data similar to excel

		FY22	FY22	FY22		FY22	FY22	FY22		FY22	FY22	FY22
Business Unit	Q4 FY21	Apr	May	Jun	Q1 FY22	Jul	Aug	Sep	Q2 FY22	Oct	Nov	Dec
BCM	63033	53764	53498	59881	55714	63365	64076	66053	64498	66685	64489	75176
Kovai	9300	9137	8536	7405	8359	9770	9534	9126	9477	9511	9380	11021

[lbendlin]

All of that is standard Matrix visual functionality.  What have you tried and where are you stuck?