How to compare columns of 2 diff. summarize tables

[Anonymous]

Hello Everyone,

I have created 2 different Summarize tables using the below DAX.

Table 1:
GroupBy_Capability=
SUMMARIZECOLUMNS (
Issues[Issue Key],
FILTER ( Issues, Issues[Issue Type] ="Capability"),
"Story Points",SUM( Issues[Story Points] )
)

Table 2:

GroupBy_Epics =
SUMMARIZECOLUMNS (
Issues[Parent Link],Issues[Issue Key],
FILTER ( Issues, Issues[Issue Type] ="EPIC"),
"Epic Story Points",SUM(Issues[Story Points] )
)

 Table 1

Issue Key	Story Points
DIG-2383	88

 Table 2:

Parent Link	Epic Story Points	Issue Key
DG-2383	5	TC-200
DG-2383	15	UL-1237
DG-2383	15	UL-1238

I have to get the difference between the Story Points in both the tables, where Table1(Issue Key)=Table2(Parent Link).

Output is 88-(5+15+15)=53.

How to compare the columns of Summarize tables as there is no relationship between theses tables?

Or should I go for Lookupvalue() or Relatedtable()?

How to use Summarize tables in a measure?

Need help  with this DAX .

Thanks in Advance

[v-yiruan-msft]

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[amitchandak]

@Anonymous , You can create a common table Issues using Issues[Issue Key] , and join with both tables  and compare

or have both in table 1

Table 1:
GroupBy_Capability=
SUMMARIZECOLUMNS (
Issues[Issue Key],
,
"Story Points",calculate(SUM( Issues[Story Points] ),FILTER ( Issues, Issues[Issue Type] ="Capability")),
"Epic Story Points",calculate(SUM(Issues[Story Points] ),FILTER ( Issues, Issues[Issue Type] ="EPIC"))
)

Now you can compare.

Table2 can be as is

[harshnathani]

Hi @Anonymous ,

Yiu cna try this measure.

Pull Parentlink from Table 2

Measure = SUM(Table1[Story Points]) - SUM(Table2[Epic Story Points])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani 

Thanks Harsha. I am getting the correct value in a measure. But is it possible to create a column as I understand this should be a row context.
And I have to find the difference % for each issue key and on top of that I have to do few more calculation on scoring part based on my %age value.

[v-yiruan-msft]

Hi @Anonymous ,

First, please create a relationship between Table 1 and Table 2 based on the field Issue Key and Parent Link.

Then create a measure and calculated column as below:

1). create a measure to get the capability store points    

Measure = CALCULATE(SUM('Table 1'[Story Points]))

2). Create a calculated column to get the difference between capability and EPIC type

Column = [Measure]- CALCULATE(SUM('Table 2'[Epic Story Points]),FILTER('Table 2','Table 2'[Parent Link]=EARLIER('Table 2'[Parent Link])))

If the above one is not what you want, please provide more sample data and your actual desired result with examples. Thank you.

Best Regards

Rena