cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RogerSteinberg
Post Patron
Post Patron

Use Summarize and then sum with an allexcept

Hi,

 

I need a measure to remove some duplicates and then sum one column grouped by another column

So the first step is the summarize and the second step which is not possible since ALLEXCEPT function does not allow for a table expression:

 

So here's  what I got so far:

totalbyCountry_without_duplicates = 
VAR _summary =

summarize (table1,start_date,totalByStartDate)


RETURN

SUMX ( ALLEXCEPT(_summary,start_date),total)

 

Simplified Sample Data:


Columns : 
Start_date, end_date, totalByStartDate, totalbyEndDate, Product

1-Jan1-Feb2010A
1-Jan1-Mar205A
1-Feb1-Mar5018A
1-Feb1-Apr509A
1-Jan1-Feb108B
1-Jan1-Mar102B
1-Feb1-Mar4012B
1-Feb1-Apr406B



7 REPLIES 7
RogerSteinberg
Post Patron
Post Patron

I created a new table with my summarize function.

And then created a measure with the ALLEXCEPT and it worked fine. But i dont want to go through this solution because of the unecessary sspace that im creating

 

Hi, @RogerSteinberg 

I guess the function summarizecolumn also works for you:

calculated table:

Table =
SUMMARIZECOLUMNS (
    table1[start date],
    table1[totalByStartDate],
    "Total", SUM ( table1[totalbyEndDate] )
)

Please check my  sample pbix file for more details.

 

If I misunderstand,please let me know

Best Regards,
Community Support Team _ Eason

So I need three things:

1. It needs to be in a measure (no table , no calculated columns)

2. It needs to summarize (which you have done)

3. It needs to have the same concept of ALLEXCEPT where the amounts are aggregated by group

Thats because I have several other columns in my actual model. For the sake of simplicity I only kept a minimum amount of columns. But if I had product, city, region and other dimensions. I'd need an allexcept function to allow the aggregation to be done for the entire group

 

I've updated my sample data in the question 

Heres the issue with my actual data 

I need the column Measure A_NUM to be divided by the total of Measure A_DEN (9998)

Measure A_DEN = 
sumx(summarize('table',dimA,dimB,dimC,dimD,sumTotal),sumTotal)

but if i take Measure A_NUM divided by Measure A_DEN i get 4293/9346 for recurring_date of Feb 2021.

2021-04-12 15_23_14-Window.png

amitchandak
Super User IV
Super User IV

@RogerSteinberg , Try a measure like

 

calculate(sumx(values(total[total]),calculate(max([total]))), allexcept(Table, Table[start Date]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Measure B : your suggestion formula

Measure A: What I had done

 

What i need is the subtotal from Measure A to appear in each row as such:

9998

9998

9998

...

RogerSteinberg_0-1617908911835.png

 

The idea is there but the value that I'm getting isn't right. I am not completely sure but the issue seems to come from the "VALUES" function which takes the distinct values of total.

 

For the sake of simplicity , my sample data only had a few columns. But my model has over 8 columns with millions of rows grouped by month for the past 4 years. For example, if , for one permutation i have a total amount of 10 for start_date Jan 1 2020 and another different permutation for the same date with the same exact amount, then I should keep both values even if they are the same. I think that using your measure it removes one of them correct?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors