Forum Discussion
DAX - Changing granularity level for SUMIF calculation
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.
Hello Anonymous
check out the file I've uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1arOlNdFWZE0Yit
let me know if this works for you
6 Replies
- LivioLanzo
Solution Sage
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?
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 - LivioLanzo
Hello Anonymous
check out the file I've uploaded: https://1drv.ms/u/s!AiiWkkwHZChHj1arOlNdFWZE0Yit
let me know if this works for you