Sum of values basis distinct value from another column

[Anonymous]

Hi ,

I want to create a measure which will give me value of a ID basis distinct.

My table is like: 

Opp	ID	Value
1	P1	5
1	P2	5
1	P3	5
2	P1	10
2	P2	10
3	P1	20
4	P1	15
5	P1	15
5	P2	15
5	P3	15
6	P1	20
6	P2	20
7	P1	10
8	P1	16
8	P2	16
9	P1	20
9	P2	20
10	P1	25
10	P2	25
10	P3	25

Output Should be
Opp	id	Value
1	P1	5
2	P1	10
3	P1	20
4	P1	15
5	P1	15
6	P1	20
7	P1	10
8	P1	16
9	P1	20
10	P1	25

[TomMartens]

Hey,

not sure if I understand you correctly, but by looking at your table and your desired output, just do the following in Power BI Desktop.

Create a Slicer visual and add the ID column to its value field

Create a Table visual and add the columns to the table in the desired sequence to the values field of the table visual, open the context menu of the Opp column and switch the aggregation to: "Don't summarize"

Hopefully this is what you are looking for.

Regards

Tom

[Anonymous]

Thanks Tom for your Quick reply.

The output was just a reference. Actually i want sum of value in a table and those value should be calculated for distict ID only.

Total value P1	156
Total value P2	111

[v-yulgu-msft]

Hi @Anonymous,

If in your result table, there are only two columns ID and Total Value, you can create a simple measure:

TotalValue = SUM(Sheet1[Value])

Then, add [ID] and this measure into table visual.

If you want to display [Opp], [ID] and total values in result table, you should create a measure like below:

TotalValue = CALCULATE(SUM(Sheet1[Value]),ALLEXCEPT(Sheet1,Sheet1[ID]))

Best regards,
Yuliana Gu

[Anonymous]

Thanks Yuliana,

However i was able to create measure for my output which is 

Measure_Proba = SUMX(VALUES(Table_RS[Opportunity ID]),CALCULATE(AVERAGE(Table_RS[RS MRC($K)]))).

It gives me total value for unique ID but when i try to put condition/filter, it doesn't give me correct output rather doubling the total value.

Measure_Proba = SUMX(VALUES(Table_RS[Opportunity ID]),CALCULATE(AVERAGE(Table_RS[RS MRC($K)]), FILTER(Table_RS,Table_RS[MRC Status]="Gross MRC" && (Table_RS[Probability (%)]=95))))

Could u please help to make the correction in my query. Or is there any way to sortout this issue.

[v-yulgu-msft]

Hi @Anonymous，

---

Measure_Proba = SUMX(VALUES(Table_RS[Opportunity ID]),CALCULATE(AVERAGE(Table_RS[RS MRC($K)]), FILTER(Table_RS,Table_RS[MRC Status]="Gross MRC" && (Table_RS[Probability (%)]=95))))

---

Please provide more description about above measure. I cannot understand those table names and column names don't exist in sample data in your original post. Besides, to help me better understand your actual requirement, please post an image to show your desired output.

Best regards,
Yuliana Gu

[Anonymous]

Hi,

Below one is the actual table which i was refereing in my query.

Opportunity ID	Probability (%)	RS MRC($K)	MRC Status
123	100	661.16	Gross MRC
123	100	661.16	Gross MRC
1234	95	124.8	Gross MRC
1234	95	124.8	Gross MRC
1234	95	124.8	Gross MRC
213	100	-410.53	Neg MRC
213	100	-410.53	Neg MRC
4321	100	-435.05	Neg MRC
4321	100	-435.05	Neg MRC
12345	95	450	Gross MRC
12345	95	450	Gross MRC
12345	95	450	Gross MRC
2653	95	-200	Neg MRC
2653	95	-200	Neg MRC
2653	95	-200	Neg MRC
1551	100	287.06	Gross MRC
1551	100	287.06	Gross MRC
1551	100	287.06	Gross MRC

And my output should be in a metrics or table like below

	Gross MRC@95	Gross MRC@100	Total Gross
Total Value :	574.8	948.22	1523.02

 When i put below query it doesn't give me above output for 1st measure

Measure_Proba = SUMX(VALUES(Table_RS[Opportunity ID]),CALCULATE(AVERAGE(Table_RS[RS MRC($K)]), FILTER(Table_RS,Table_RS[MRC Status]="Gross MRC" && (Table_RS[Probability (%)]=95))))

Hope this will help to understand my exact requirement.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try:

Gross MRC@95 =
SUMX (
    SUMMARIZE (
        FILTER (
            Table_RS,
            Table_RS[MRC Status] = "Gross MRC"
                && ( Table_RS[Probability (%)] = 95 )
        ),
        Table_RS[Opportunity ID],
        "RS MRC($K)2", AVERAGE ( Table_RS[RS MRC($K)] )
    ),
    [RS MRC($K)2]
)

Gross MRC@100 =
SUMX (
    SUMMARIZE (
        FILTER (
            Table_RS,
            Table_RS[MRC Status] = "Gross MRC"
                && ( Table_RS[Probability (%)] = 100 )
        ),
        Table_RS[Opportunity ID],
        "RS MRC($K)2", AVERAGE ( Table_RS[RS MRC($K)] )
    ),
    [RS MRC($K)2]
)

Total Gross = [Gross MRC@95]+[Gross MRC@100]

Best regards,
Yuliana Gu

[Anonymous]

Thanks Yuliana,

It worked for me.

[Anonymous]

Hi,

I want to calculate a measure which will give me Sum value for unique ID only basis certain criteria/filter.

My table is like:

Opp	ID	Value
1	P1	5
1	P2	5
1	P3	5
2	P1	10
2	P2	10
3	P1	20
4	P1	15
5	P1	15
5	P2	15
5	P3	15
6	P1	20
6	P2	20
7	P1	10
8	P1	16
8	P2	16
9	P1	20
9	P2	20
10	P1	25
10	P2	25
10	P3	25

	Output Should be
Opp	id	Value
1	P1	5
2	P1	10
3	P1	20
4	P1	15
5	P1	15
6	P1	20
7	P1	10
8	P1	16
9	P1	20
10	P1	25

[Greg_Deckler]

You should be able to just create a table visualization and put Opp in it and set it to "Do not summarize". Then place ID and Value in it and filter ID to P1. Shouldn't need a measure or anything else.