Christian has attended:
Excel PowerPivot course
Power BI Modelling, Visualisation and Publishing course
Dax Measures to Sum total values paid by members, then segment a
I have list of members + donors, who pay various amounts through the year.
I need to create a DAX measure that will SUM the totals given by each member....
THEN I need to segment the TOTALS BY DONOR, ie "Less than £5k", £5k to £10"
THEN I need to count the number of donors in each segment. ie, to give results such as: 55 donors £5k to £10k total, 25 donors £10k to £25k etc etc.....
I have managed to do all the above using Power Query to group, but I am going by the rule of Power Query (M formulas) should be the data prep stage and that DAX is the analysis stage. All of what I want to do above is not data prep, it's data analysis.
Help please!
thanks, Christian
RE: Dax Measures to Sum total values paid by members, then segme
Hi Christian,
Thank you for the forum question.
It is all about how you have created your data model.
If you look at the two attached files you will find I have created a frequency table. In the lookup table tblId i have a unique donor list. I am calculating the sum of all donations in the tblAmount table and to calculate it for each donor, I in the sumx function reference the tblId table. Then I make a key in the tblId table to make the relationship to the frequency table.
I have attached an Excel file with the source data and in the same file you will find a PowerPivot solution. The second file is a Power Bi file with connection to the Excel, and here you will find a Power Bi solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
frequency.xlsx
