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 1ITEMVALUEDATEA1201/01/2019B301/01/2019C201/01/2019D201/01/2019E401/01/2019F301/01/2019G401/01/2019H701/01/2019I901/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 2ITEMVALUEDATEA1201/01/2019A1102/01/2019A1103/01/2019B301/01/2019B302/01/2019C206/01/2019C407/01/2019C208/02/2019C209/02/2019D201/01/2019D102/01/2019F413/01/2019F318/01/2019G405/01/2019T406/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: ITEMVALUEDATEA1201/01/2019B301/01/2019C201/01/2019D201/01/2019F301/01/2019G401/01/2019SUM >>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!!

Helper 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, 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!!

Need Help

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.

Divide_v2.pbix82 KB

4 Replies

v-juanli-msft

Community Support

7 years ago

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.

Divide filtered values (by other table) by total values.pbix76 KB

v-juanli-msft
Community 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
  Helper 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).
  
  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

Community Support

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.

Divide_v2.pbix82 KB

Forum Discussion

Divide filtered values (by other table) by total values

4 Replies

Related content

divide 2 values

Measure to DIVIDE two columns with filtered values

Divide With a Filter

Dividing FIltered Value

Divide with Filter

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