Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Aggregate values by Date selections

<span;>Hi, I have the following table below. As it stands using one days worth of data, this table works perfectly. My % column takes the value of a singular row and divides by the total of the value by ID.
<span;>
<span;>%=DIVIDE(
<span;>    [Value],
<span;> CALCULATE(
<span;>        SUM(Table1[Value]),
<span;>        ALLEXCEPT(Table1, Table1[ID])))

<span;>ID.   Value.   %
<span;>1         100.  10%
<span;>1.        100.  10%
<span;>1.         300.  30%
<span;>1.         500.  50%
<span;>2.         200.  40%
<span;>2.         100.  20%
<span;>2.          200. 40%

<span;>However, when I add another days worth of data in but filter on just one day (tw_date), the value reacts to the latest date and sums it correctly but the % column doesn't and it returns the percentage value based on the 2 days.

<span;>Any idea how to change this please so it reacts to the dates selected please

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    M_SBS_6 Well, I *think* the issue is your ALLEXCEPT removing the date context so probably need to include the column your slicer is based on within your ALLEXCEPT?

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    M_SBS_6 Well, I *think* the issue is your ALLEXCEPT removing the date context so probably need to include the column your slicer is based on within your ALLEXCEPT?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi M_SBS_6 

     

    I added another date to the data you provided.

     

    You can change the formula to something like this:

    % = DIVIDE([Value], CALCULATE(SUM(Table1[Value]), ALLEXCEPT(Table1, Table1[ID], Table1[Date])))

     

    Is this the result you expect?

     

    Best Regards,
    Community Support Team _Yuliax

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.