Forum Discussion
TonyBI
Helper I
Helper IDivide filtered values (by other table) by total values
Estimates, thanks in advance and sorry for my english. Here is the thing, I have a table with values my month and item, as shown (this table contain every month, but the estructure is the same - item...
Hi TonyBI
Based on my previous solution, i add the following steps to acheive your second goal.
1.create a column in Table1
month_total = CALCULATE(SUM(Table1[VALUE]),ALLEXCEPT(Table1,Table1[YEAR/MONTH1]))
2. create measures in Table2
Measure 2 = CALCULATE(MAX(Table2[DATE]),ALLEXCEPT(Table2,Table2[ITEM],Table2[YEAR/MONTH2])) Measure 3 = CALCULATE(SUM(Table2[VALUE]),FILTER(Table2,Table2[DATE]=[Measure 2])) Measure 4 = SUMX(ALLEXCEPT(Table2,Table2[YEAR/MONTH2]),[Measure 3]) Measure 5 = CALCULATE(MAX(Table1[month_total]),FILTER(Table2,Table2[YEAR/MONTH2]=MAX(Table1[YEAR/MONTH1]))) Measure 6 = IF([Measure 5]<>BLANK(),[Measure 4]/[Measure 5])
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft
Community Support
Community SupportHi TonyBI
Based on my previous solution, i add the following steps to acheive your second goal.
1.create a column in Table1
month_total = CALCULATE(SUM(Table1[VALUE]),ALLEXCEPT(Table1,Table1[YEAR/MONTH1]))
2. create measures in Table2
Measure 2 = CALCULATE(MAX(Table2[DATE]),ALLEXCEPT(Table2,Table2[ITEM],Table2[YEAR/MONTH2])) Measure 3 = CALCULATE(SUM(Table2[VALUE]),FILTER(Table2,Table2[DATE]=[Measure 2])) Measure 4 = SUMX(ALLEXCEPT(Table2,Table2[YEAR/MONTH2]),[Measure 3]) Measure 5 = CALCULATE(MAX(Table1[month_total]),FILTER(Table2,Table2[YEAR/MONTH2]=MAX(Table1[YEAR/MONTH1]))) Measure 6 = IF([Measure 5]<>BLANK(),[Measure 4]/[Measure 5])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.