How to apply value of TOTAL to every QUARTER for dividing (get % of) by every QUARTER TOTAL

[rane19]

Goal :

Repeating "Target" in every quarter compare to quarterly "Actual" by apply "Date.Quarter" Axis in cluster chart.

Problem :

Able to achieving "Target (value only 1row in Q1) vs Actual (every rows) %" in scoreard . But cannot apply of line chart with "Shared azis" by [Date].[Quarter]

# Table name : "data"

Quarterly	Year	Target	Actual
Q1	2020/01/01	900	200
Q2	2020/04/01		210
Q3	2020/09/01		205
Q4	2020/12/01		230
Q1	2021/02/01	800	203
Q2	2021/06/01		214
Q3	2021/08/01		220
Q4	2021/12/01		255

Image :

DAX 

Since REMOVEFILTERS() or VAR -- RETURN operation work against the filter of "Date.Quarter" Axis ,

no normal DAX expression I can find to manipulate the "Target" value to every quarter. 

Or the only way is prepare another data table from scracth  which duplibcate "Target" x 4 then match 4Q Date? (seems not productive )

[rane19]

@amitchandak  appreciate a lot definitely it is a great method but my bad for didn't obviously explain that my Goal is letting the Year Target repeatdly show in every quarter .(Different from the idea of "disturbute")  That you can imagine that 1 annual target show 4 times in the dataset of every quarter starting which quite illogical but that is my goal . I may have to process another separate dataset before import to Power BI which contain this illgoical shape to achieve the goal of this chart  .

[amitchandak · Accepted solution]

@rane19 , Move target on last date of year nad you can use

CLOSINGBALANCEYEAR - as given in blog

Also this should work

This Year = CALCULATE(sum('Table'[Target]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

prefer separate date/qtr year table

[amitchandak]

@rane19 , Seem like you have yearly target ,

Using date table you can distribute it in table or measure.

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...