Divide filtered values (by other table) by total values

[TonyBI]

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, value, month - I only show january here):

TABLE 1

ITEM	VALUE	DATE
A	12	01/01/2019
B	3	01/01/2019
C	2	01/01/2019
D	2	01/01/2019
E	4	01/01/2019
F	3	01/01/2019
G	4	01/01/2019
H	7	01/01/2019
I	9	01/01/2019
…

The above table contains every value for every item (unique values) for every month. Now, I have another table that contains a number of the above items, and in some cases they are repeated (the values here are kind of certified values of the table 1)

TABLE 2

ITEM	VALUE	DATE
A	12	01/01/2019
A	11	02/01/2019
A	11	03/01/2019
B	3	01/01/2019
B	3	02/01/2019
C	2	06/01/2019
C	4	07/01/2019
C	2	08/02/2019
C	2	09/02/2019
D	2	01/01/2019
D	1	02/01/2019
F	4	13/01/2019
F	3	18/01/2019
G	4	05/01/2019
T	4	06/02/2019

I need to do two things...

1. I need to be able to sum the values of the first table but only for those items in the sencond table in the same month... this would be the next table:

ITEM	VALUE	DATE
A	12	01/01/2019
B	3	01/01/2019
C	2	01/01/2019
D	2	01/01/2019
F	3	01/01/2019
G	4	01/01/2019
SUM >>	26

2. the other thing that would be nice to do is to sum the latest value found on TABLE 2 (for every month) and divide it by the total sum of TABLE 1 (for the same month). This way I would be able to know, for every month, the percentage of certified values from the total.

Thanks!!

[v-juanli-msft · Accepted solution]

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]

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]

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

ITEM	VALUE	DATE
A	12	01/01/2019
A	11	01/02/2019
A	11	01/03/2019
B	4	01/01/2019
B	3	01/02/2019
C	2	01/01/2019
C	4	01/03/2019
C	2	01/08/2019
C	2	09/02/2019
D	2	01/01/2019
D	1	01/11/2019
F	4	12/01/2019
F	3	01/14/2019
G	4	05/01/2019
T	4	06/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:

ITEM	VALUE	DATE
A	11	01/03/2019
B	3	01/02/2019
C	2	01/08/2019
D	1	01/11/2019
F	3	01/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!

[v-juanli-msft]

Hi @TonyBI 

As there is no value for Item C and G at 201901 in your example data, so the output of the sum total for 201901 is 20 in my test.

Look at my test:

1. create two columns in tablea and table2

columns in table 1

YEAR/MONTH1 = FORMAT(Table1[DATE],"YYYYMM")

Column+merge1 = Table1[ITEM]&FORMAT(Table1[DATE],"yyyymm")

columns in table2

YEAR/MONTH2 = FORMAT(Table2[DATE],"YYYYMM")

Column+merge2 = Table2[ITEM]&FORMAT(Table2[DATE],"yyyymm")

2. create relationship as below

3.create a measure in table2

Measure =
VAR SUM1 =
    CALCULATE (
        SUM ( Table1[VALUE] ),
        ALLEXCEPT ( Table2, Table2[ITEM], Table2[YEAR/MONTH2] )
    )
VAR SUM2 =
    SUMX (
        ALL ( Table1 ),
        CALCULATE (
            SUM ( Table1[VALUE] ),
            ALLEXCEPT ( Table1, Table1[ITEM], Table1[YEAR/MONTH1] )
        )
    )
RETURN
    IF (
        ISINSCOPE ( Table2[ITEM] ),
        SUM1,
        IF ( ISINSCOPE ( Table2[YEAR/MONTH2] ), SUM2, 0 )
    )

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.