Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
HI @Anonymous
Try this:
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/
Unfortunatly that did not work.
@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/
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% |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |