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.
Hi everyone,
I am stuck on something in DAX and this is driving me crazy.
Here is my problem:
I have the below data: Team/Snapshot/Over/Current/Total.
I know how to calculate Daily% and RollAvg% in DAX.
But I need to do something else (please refer to 2nd screenshot)
This is the summarized version of the previous table at the Date/Snapshot level.
Now I need to recalculate my measures.
However, when summing up Over and Total, I need to exclude values that have Exclusion Flag = 0 in the previous table.
From there, I can then calculate my Daily %
And THEN, I want can calculate my Rolling Avg which is based on the newly re-calculated Daily%.
There is an important requirement here: we need to calculate the Avg of the Daily %. Meaning that we need to calculate the Daily% FIRST and then average them.
I only need help calculating RollAvg % at the Snapshot Level.
Anyone knows how to do this ?
Thanks in advance,
Jason.
Solved! Go to Solution.
Hello @Anonymous
check out the file I've uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1arOlNdFWZE0Yit
let me know if this works for you
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
are you trying to return a table or doing it in a matrix? can you post a table which can be copy pasted?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
I am trying to build a Matrix.
TEAM | Snapshot Level | Over | Current | Total | Daily % | RollAvg % | Exclusion Flag |
TeamA | 2018/12/19 | $ 1,216,614.00 | $ 432,697.00 | $ 1,649,311.00 | 73.76% | 73.76% | 1 |
TeamA | 2018/12/20 | $ 1,276,536.00 | $ 482,463.00 | $ 1,758,999.00 | 72.57% | 73.17% | 0 |
TeamA | 2018/12/21 | $ 1,296,174.00 | $ 434,994.00 | $ 1,731,168.00 | 74.87% | 73.74% | 1 |
TeamA | 2018/12/22 | $ 1,264,147.00 | $ 468,026.00 | $ 1,732,173.00 | 72.98% | 73.55% | 1 |
TeamA | 2018/12/23 | $ 1,243,103.00 | $ 419,726.00 | $ 1,662,829.00 | 74.76% | 73.79% | 1 |
TeamB | 2018/12/19 | $ 410,890.00 | $ 19,124,489.00 | $ 19,535,379.00 | 2.10% | 2.10% | 1 |
TeamB | 2018/12/20 | $ 498,982.00 | $ 18,379,548.00 | $ 18,878,530.00 | 2.64% | 2.37% | 1 |
TeamB | 2018/12/21 | $ 474,621.00 | $ 17,911,327.00 | $ 18,385,948.00 | 2.58% | 2.44% | 0 |
TeamB | 2018/12/22 | $ 425,197.00 | $ 19,423,492.00 | $ 19,848,689.00 | 2.14% | 2.37% | 1 |
TeamB | 2018/12/23 | $ 492,982.00 | $ 18,855,618.00 | $ 19,348,600.00 | 2.55% | 2.40% | 1 |
TeamC | 2018/12/19 | $ 1,798.00 | $ 713.00 | $ 2,511.00 | 71.60% | 71.60% | 1 |
TeamC | 2018/12/20 | $ (1,152.00) | $ 1,818.00 | $ 666.00 | -172.97% | -50.68% | 1 |
TeamC | 2018/12/21 | $ (4,152.00) | $ (565.00) | $ (4,717.00) | 88.02% | -4.45% | 1 |
TeamC | 2018/12/22 | $ (2,991.00) | $ (841.00) | $ (3,832.00) | 78.05% | 16.18% | 1 |
TeamC | 2018/12/23 | $ 694.00 | $ (1,455.00) | $ (761.00) | -91.20% | -5.30% | 0 |
TeamD | 2018/12/19 | $ 2,295,400.00 | $ 15,720,276.00 | $ 18,015,676.00 | 12.74% | 12.74% | 1 |
TeamD | 2018/12/20 | $ 2,009,030.00 | $ 15,171,769.00 | $ 17,180,799.00 | 11.69% | 12.22% | 1 |
TeamD | 2018/12/21 | $ 2,126,146.00 | $ 15,303,235.00 | $ 17,429,381.00 | 12.20% | 12.21% | 1 |
TeamD | 2018/12/22 | $ 2,376,371.00 | $ 15,322,171.00 | $ 17,698,542.00 | 13.43% | 12.52% | 1 |
TeamD | 2018/12/23 | $ 2,298,318.00 | $ 15,011,664.00 | $ 17,309,982.00 | 13.28% | 12.67% | 1 |
TeamE | 2018/12/19 | $ 2,000,695.00 | $ 30,082,221.00 | $ 32,082,916.00 | 6.24% | 6.24% | 1 |
TeamE | 2018/12/20 | $ 2,075,585.00 | $ 30,408,035.00 | $ 32,483,620.00 | 6.39% | 6.31% | 1 |
TeamE | 2018/12/21 | $ 2,163,131.00 | $ 30,944,277.00 | $ 33,107,408.00 | 6.53% | 6.39% | 1 |
TeamE | 2018/12/22 | $ 2,010,620.00 | $ 30,501,306.00 | $ 32,511,926.00 | 6.18% | 6.34% | 0 |
TeamE | 2018/12/23 | $ 2,264,367.00 | $ 29,956,934.00 | $ 32,221,301.00 | 7.03% | 6.47% | 1 |
TeamF | 2018/12/19 | $ 156,585.00 | $ 731,440.00 | $ 888,025.00 | 17.63% | 17.63% | 1 |
TeamF | 2018/12/20 | $ (123,253.00) | $ 854,353.00 | $ 731,100.00 | -16.86% | 0.39% | 1 |
TeamF | 2018/12/21 | $ (20,914.00) | $ 863,498.00 | $ 842,584.00 | -2.48% | -0.57% | 1 |
TeamF | 2018/12/22 | $ (83,613.00) | $ 795,776.00 | $ 712,163.00 | -11.74% | -3.36% | 0 |
TeamF | 2018/12/23 | $ (110,426.00) | $ 875,583.00 | $ 765,157.00 | -14.43% | -5.58% | 0 |
Hello @Anonymous
check out the file I've uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1arOlNdFWZE0Yit
let me know if this works for you
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous, it is good practice to use a Date table in your model when you have a date column in the fact table
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |