Forum Discussion

TonyBI's avatar
TonyBI
Icon for Helper I rankHelper I
7 years ago
Solved

Divide 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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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
    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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi TonyBI 

I don't undertsand this " the other thing that would be nice to do is to sum the latest value found on TABLE 2 (for every month)".

Please give me an example based on the example data.

Let to say, for item A in 201901?

 

Best Regards
Maggie

  • TonyBI's avatar
    TonyBI
    Icon for Helper I rankHelper I
    7 years ago

    Maggie! thx very much!

     

    To clarify the second part of the question, this is what I mean:

     

    Table 2 has every value for each month (I changed the values to be more specific - Notice that some items have many values for the same month, but diffent days). 

     

    ITEMVALUEDATE
    A1201/01/2019
    A1101/02/2019
    A1101/03/2019
    B401/01/2019
    B301/02/2019
    C201/01/2019
    C401/03/2019
    C201/08/2019
    C209/02/2019
    D201/01/2019
    D101/11/2019
    F412/01/2019
    F301/14/2019
    G405/01/2019
    T406/02/2019

     

    So, I would like to sum Every last value for each item, each month. In this case (january only), this should be the result:

     

    ITEMVALUEDATE
    A1101/03/2019
    B301/02/2019
    C201/08/2019
    D101/11/2019
    F301/14/2019

     

    Total sum should be 20. Finally, I would divide "20" by total values of table 1 ("46" for january).

     

    This will allow me to know the percentage of certified values by month.

     

    Thx in advance!