topic Re: How do you use the Average line results created in from the analytic tab in a calculation? in DAX Commands and Tips

How do you use the Average line results created in from the analytic tab in a calculation?

Anonymous — Thu, 25 Nov 2021 19:28:36 GMT

I have created a Area line chart and included an average line which works great. What I want to do now is include a calculation that will take the results of the average line witch will create another line that will represent the % difference for average.

I can manualy include the number in the calculation but the data it going to change based on the filtering therefor the average will also change.

This is the calculation I am using.

Case_Count % difference from SLR-06 =

VAR __BASELINE_VALUE = 2357

VAR __MEASURE_VALUE = SUM('SLRCaseData'[Case_Count])

RETURN

IF(

NOT ISBLANK(__MEASURE_VALUE),

DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

)

Re: How do you use the Average line results created in from the analytic tab in a calculation?

VahidDM — Thu, 25 Nov 2021 22:03:50 GMT

HI Anonymous

Try this:

Case_Count % difference from SLR-06 =

VAR __BASELINE_VALUE = calculate(SUM('SLRCaseData'[Case_Count]),removefilters('SLRCaseData'[Date Column]))

VAR __MEASURE_VALUE = SUM('SLRCaseData'[Case_Count])

RETURN

IF(

NOT ISBLANK(__MEASURE_VALUE),

DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)

)

Date Column: change this to the column you used as an axis of the visual.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: How do you use the Average line results created in from the analytic tab in a calculation?

Anonymous — Thu, 25 Nov 2021 23:22:50 GMT

Unfortunatly that did not work.

VAR __BASELINE_VALUE = CALCULATE(sum('SLRCaseData'[Case_Count]),Removefilters(SLRCaseData[Closed Date]))

I am thinking that the Baseline_Value calculation is not returning the correct result based on the filters applied for the time frame. When using the analstics/average line it shows a consist result "2357" accross the entire time frame. If I could recreate that calculation.

Re: How do you use the Average line results created in from the analytic tab in a calculation?

VahidDM — Thu, 25 Nov 2021 23:47:44 GMT

Anonymous

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: How do you use the Average line results created in from the analytic tab in a calculation?

Anonymous — Wed, 01 Dec 2021 16:11:40 GMT

My expected out come is;

Line A - To have a line with the total number of cases for each month for the time span selected. In this case 1 year.

Line B - To have a Monthly average line representing the average number of case counts expected to recieve each month - nice flat line.

Finially a secondary line that would represent the % difference from Line A and line B.

Year	Month	Case_Count	Amdocs	Soft_IMAC	sum of cases for year /12	%inc/decr
2018	January	2133	384	1749	2479	-16%
2018	February	1639	268	1371	2479	-51%
2018	March	2000	317	1683	2479	-24%
2018	April	2382	412	1970	2479	-4%
2018	May	2451	383	2068	2479	-1%
2018	June	2292	347	1945	2479	-8%
2018	July	2397	345	2052	2479	-3%
2018	August	2768	422	2346	2479	10%
2018	September	2849	427	2422	2479	13%
2018	October	3316	432	2884	2479	25%
2018	November	3270	370	2900	2479	24%
2018	December	2256	298	1958	2479	-10%
2019	January	2902	348	2554	2996	-3%
2019	February	2412	339	2073	2996	-24%
2019	March	2571	400	2171	2996	-17%
2019	April	2646	425	2221	2996	-13%
2019	May	3366	444	2922	2996	11%
2019	June	2819	409	2410	2996	-6%
2019	July	2874	394	2480	2996	-4%
2019	August	2881	416	2465	2996	-4%
2019	September	3527	341	3186	2996	15%
2019	October	3828	413	3415	2996	22%
2019	November	3535	361	3174	2996	15%
2019	December	2594	298	2296	2996	-16%

Re: How do you use the Average line results created in from the analytic tab in a calculation?

Anonymous — Wed, 01 Dec 2021 13:06:27 GMT

Calculate the average based sum of month over the year.

Line A - To have a line with the total number of cases for each month for the time span selected. In this case 1 year.

Line B - To have a Monthly average line representing the average number of case counts expected to recieve each month - nice flat line.

Finially a secondary line that would represent the % difference from Line A and line B.

Year	Month	A	Amdocs	Soft_IMAC	B - sum of cases for year /12	%inc/decr
2018	January	2133	384	1749	2479	-16%
2018	February	1639	268	1371	2479	-51%
2018	March	2000	317	1683	2479	-24%
2018	April	2382	412	1970	2479	-4%
2018	May	2451	383	2068	2479	-1%
2018	June	2292	347	1945	2479	-8%
2018	July	2397	345	2052	2479	-3%
2018	August	2768	422	2346	2479	10%
2018	September	2849	427	2422	2479	13%
2018	October	3316	432	2884	2479	25%
2018	November	3270	370	2900	2479	24%
2018	December	2256	298	1958	2479	-10%
2019	January	2902	348	2554	2996	-3%
2019	February	2412	339	2073	2996	-24%
2019	March	2571	400	2171	2996	-17%
2019	April	2646	425	2221	2996	-13%
2019	May	3366	444	2922	2996	11%
2019	June	2819	409	2410	2996	-6%
2019	July	2874	394	2480	2996	-4%
2019	August	2881	416	2465	2996	-4%
2019	September	3527	341	3186	2996	15%
2019	October	3828	413	3415	2996	22%
2019	November	3535	361	3174	2996	15%
2019	December	2594	298	2296	2996	-16%

Hope this helps

Re: How do you use the Average line results created in from the analytic tab in a calculation?

v-jingzhang — Thu, 02 Dec 2021 08:14:06 GMT

Hi Anonymous

Try these measures

Avg of Year = VAR vYear = MAX('Table'[Year]) RETURN CALCULATE(SUM('Table'[Case_Count]),ALL('Table'),'Table'[Year]=vYear)/12

Case_Count % difference from SLR-06 = VAR __BASELINE_VALUE = [Avg of Year] VAR __MEASURE_VALUE = SUM('Table'[Case_Count]) RETURN IF( NOT ISBLANK(__MEASURE_VALUE), DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE) )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.