Forum Discussion
Conditional Sum
Hi, I want to get sum of some rows from a given data set. I can better explain my query from the following example.
In the image attached, I have a dataset containing three names A, B, and C. They all have values of for every single day. In the Conditional Sum column, on a given date, I want to calculate the sum of the curves A and B. For instance, on 25,26, and 27 July 2022, the value will be 23, 41, and 59 respectively.
I am also wondering how would we treat the missing rows. To put in other words, against three rows, we are generating only one row of output that is adding A and B and leaving C. And then this process continues for the other dates.
Please help me in computing this in Power BI. Thank you.
Regards,
Muhammad Usman
Hi, Anonymous ;
Try to create those measures.
A = CALCULATE(SUM('Table'[Value]),'Table'[Name]="A")B = CALCULATE(SUM('Table'[Value]),'Table'[Name]="B")C = CALCULATE(SUM('Table'[Value]),'Table'[Name]="C")D = calculate(Sum('Table'[Value]), filter(allselected('Table'), [Date] = max('Table'[Date]) && [Name] in {"A", "B"} ) )ratdio = [D]/[C]The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Anonymous , You can create a measure like
calculate(Sum(Table[Value]), filter(allselected(Table), Table[Date] = max(Table[Date]) && Table[Name] in {"A", "B"} ) )
Can you repost your data as text please (not a picture)? and can you show what the desired result is
Hi HotChilli,
Here is the data format in the tabular format. In short, I want to create a new curve which will be sum of the curve A and Curve B only. To give one example, on 25 July, I want to add the values of A and B which are 10 and 13. Similary, I want to do it for all the dates.
To be more concise, a new curve is required which is daily sum of the Curves A and B.
P.S: Not the objective of this post: Later on, I want to divide the value of this new curve by C and generate another curve which is ultimate objective.
Name Date Value Conditional Sum A 25-Jul-22 10 B 25-Jul-22 13 C 25-Jul-22 16 A 26-Jul-22 19 B 26-Jul-22 22 C 26-Jul-22 25 A 27-Jul-22 28 B 27-Jul-22 31 C 27-Jul-22 34
The data is helpful, thank you.
The 2nd part of my question has not been answered:
Please show me what the desired output is
Hi HotChilli,
The following could be one of the possible outcomes.Let me write a few lines for explanation as well. I have created a new column for the new curve which is D. Then in the Conditional Sum column, for a given date, we shall add the value of A and B. About the phrase Can't decide, actually this is one of the problems of this post. As we can observe that adding the values from two rows into one row will leave one empty line blank. I have absolutely no clue how this should or would be tackled in Power BI.
Name Date Value New Curve Conditional Sum A 25-Jul-22 10 D 23 B 25-Jul-22 13 D Can't decide C 25-Jul-22 16 D Can't decide A 26-Jul-22 19 D 31 B 26-Jul-22 22 D Can't decide C 26-Jul-22 25 D Can't decide A 27-Jul-22 28 D 59 B 27-Jul-22 31 D Can't decide C 27-Jul-22 34 D Can't decide I hope I have clarified the problem. Looking forward to listening back from you.
Many thanks.
Thanks for the quick update.
"I have absolutely no clue how this should or would be tackled in Power BI" - what is most important is the result that YOU want.
I can see in the updated data posted that there is already a desire to combine the A,B result into the A column. Is it relevant to show the other lines?
Maybe you want a result like this
Date Cond_Sum
25th 23
26th 31
27th 59
I'm not around for a few hours but will respond later if you need more help.
Hi, Anonymous ;
Try to create those measures.
A = CALCULATE(SUM('Table'[Value]),'Table'[Name]="A")B = CALCULATE(SUM('Table'[Value]),'Table'[Name]="B")C = CALCULATE(SUM('Table'[Value]),'Table'[Name]="C")D = calculate(Sum('Table'[Value]), filter(allselected('Table'), [Date] = max('Table'[Date]) && [Name] in {"A", "B"} ) )ratdio = [D]/[C]The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
