Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
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 |
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
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.
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
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.
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
Thanks Yuliana,
It worked for me.
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 |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
83 | |
70 |