topic Re: How to apply value of TOTAL to every QUARTER for dividing (get % of) by every QUARTER TOTAL in DAX Commands and Tips

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

rane19 — Mon, 21 Feb 2022 01:17:45 GMT

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 )

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

amitchandak — Mon, 21 Feb 2022 02:53:22 GMT

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-Convert-to/ba-p/1476400

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

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

rane19 — Mon, 21 Feb 2022 07:27:27 GMT

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 .

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

amitchandak — Mon, 21 Feb 2022 08:37:59 GMT

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