Forum Discussion
Impactful IndividualGrouping without summarize and calculated table
Hello,
according to sqlbi https://www.sqlbi.com/articles/all-the-secrets-of-summarize/#
the summarize function better should not be used to create a column, where I can sumx, countx etc. from.
But I still don't see a better way.
For example if I have three columns which I want to group by what is the best way to do it in a measure?
The grouped table looks like this:
summarize(Table'Table'[Column1],'Table'[Colum2], "SalesAmount",'Table'[Colum3]))
Now for sum up the newly created grouped sales amount as "SalesAmount"of that table I wrap it into a x-aggregated function like this:
sumx(summarize(Table'Table'[Column1],'Table'[Colum2], "SalesAmount",'Table'[Colum3])), [SalesAmount])
I really do think thats not even close to be the elegant way, and according to the sqlbi I might get issues afterwards in many filtercontext situation. Also in the Dax-Guide library the "name" part of the summarize-syntax is described as "depreciated". So I would be better off, not to use it I guess.
Anyone has a alternative much better way?
Thanks in advance.
Best.
Applicable88 this is what I will do (if I understood the question correctly).
Create a base measure to get distinct count
d = COUNTROWS ( VALUES ( Tabelle1[MissionID] ) )to get the sum of total distinct count values I will use this measure
d = COUNTROWS ( VALUES ( Tabelle1[MissionID] ) )to get the distinct count for each date & status I will use this measure
c = COUNTX ( SUMMARIZE ( Tabelle1, Tabelle1[Date], Tabelle1[Status] ), [d] )This way we have a base measure created once (distinct count) and then used in other measures.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
16 Replies
PaulDBrownCommunity Champion
As parry2k states, you may not need to use the SUMMARIZE function for your calculation. (It would be helpful if you provided sample data and a depiction of your expected outcome so we can provide further help).
As regards the recommendation from the gurus at SQLBI, what they are advocating (to avoid potential unexpected outcomes) is to use SUMMARIZE only as a grouping function. If you need to include an aggregation as a column, they suggest the use of ADDCOLUMNS & SUMMARIZE.So an example of this would be something along the lines of:
measure =
SUMX(
ADDCOLUMNS(
SUMMARIZE(Table, Table[column1], Table[Column2], Table[column3]),
"@Total", [measure1]),
[@Total])
Again, this is probably not necessary in your particular measure.
- parry2k
Super User
Applicable88 this is what I will do (if I understood the question correctly).
Create a base measure to get distinct count
d = COUNTROWS ( VALUES ( Tabelle1[MissionID] ) )to get the sum of total distinct count values I will use this measure
d = COUNTROWS ( VALUES ( Tabelle1[MissionID] ) )to get the distinct count for each date & status I will use this measure
c = COUNTX ( SUMMARIZE ( Tabelle1, Tabelle1[Date], Tabelle1[Status] ), [d] )This way we have a base measure created once (distinct count) and then used in other measures.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Applicable88Hi parry2k, sorry for the late reply. The second solution of yours is the same formula as the first one.
"to get the sum of total distinct count values I will use this measure"
What would be your preferred way to return the sum of the missions?
Thanks so far.
Best.
- parry2k
Applicable88 sorry by mistake copied the same DAX twice, here it is:
s = SUMX ( Tabelle1, [d] )Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Applicable88
Hello parry2k ,
thanks so much for the effort. I will try it out mark the whole thread as solved, when there is nothing more to add. I come closer to the understanding why you always emphasis doing the measure [d] first.
I thought putting a measure into another calculated table or other measure is like always the same, as putting the underlying formula of that measure itself into it. But seemingly not in every case. In some measure I wrote the whole formula of [d] into another calculation and I got totally different returns.
- parry2k
Applicable88 why you want to do this, just simple SUM will work and based on whatever level you visualize this measure it will work, if you put a table visual, add column1 and column2, SUM will be grouped by these two columns and if you only use column1 , it will be group column1. I'm not sure what is the end goal and what you are trying to solve.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Applicable88
Hi parry2k,
thanks for your input,
since I'm coming from Qlik, I find it very confusing to always add a new table. The processing at runtime if you have many calculated tables also takes a while to load. Especially like my example, in case i have a year and a category columnn and want to sum the total sales of it just for one card visual, and don't need the table for any further calculation or visuals, isn't it a waste as to have a good way to put it directly as a measure?
If that is the best practice in PB, I seemingly need to get used to it.
Still hope there are better ways.
Best.
- parry2k
Applicable88 Don't know how things work in Qlik but in your example, you don't need to add a calculated table at all. Most of the stuff you want to focus is on creating the measures and you need to understand the concept of Row context, given the example, one sum measure will get you what you are looking for regardless of how you view the data, grouping by column1 or column2 together or separately or without any column.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
Thanks, PaulDBrown for chiming in but I don't understand the reason to even create a measure you mentioned if you are just looking at the sum of two columns, a simple sum will take care of it, isn't it until it is explicit that SUM value is only required for these two columns. Maybe that is what Applicable88 looking for but in my opinion, it is a straightforward SUM and then visualizes whatever way you want.
- PaulDBrown
Sure, I agree. (and stated so in my previous response)
The only reason I posted the example was to try to explain what the guys at SQLBI are advocating (which came as a bit of a shock, given the dozens of times I have added a calculated column to a SUMMARIZE).
Again, probably completely overkill with regards the measure the OP is looking for.
Anyway, my apologies if my response has prompted confusion. - Applicable88
thanks parry2k and PaulDBrown both for your ideas. I will provide a sample table for this and might clarify my situation which right. But back to parry2k, when you talking about a sum it might be enough, the total sales might be same grouped or not grouped. In case I need a count, different grouping combinations defintely creates different amount of rows. I don't think that is manageable in my case. Also the limit for a card visual is that you can only draw one column into it. A table visual might have more possibilities when needed.
I will create a sample data to show, what problem I have to solve.
Thx
- KNP
Applicable88 - I created a simple PBIX file here which I think explains exactly what parry2k is saying. Based on the example given this simple measure should work in all visualisations, if your scenario is actually more complicated, then sample data will certainly help.
- Applicable88
Hello KNP ,
thanks for the effort! I updated my reply with a sample pbix myself. Looking at your grouping in the visuals, sum can be used, because the grouping won't change the total sum. But I'm still think that this simplified apporach will get to my values. The problem lies in the kind of how I need to count. I hope you can take a look:
https://drive.google.com/drive/folders/1ZjzefXohslwuz5iXg99np8DOXChzUt4g?usp=sharing
Thank you.
Best.
- KNP
Hi Applicable88,
Disclaimer: DAX is not my strong suit, typically I would solve any complex issues in Power Query (just because that's where my skillset sits).
Thanks for the PBIX file, it helps my understanding of the issue a lot.
At first glance, I'd say you could simplify the measures by turning them into a COUNTROWS() and SUMMARIZECOLUMNS()...
[Measure 3] = COUNTROWS ( SUMMARIZECOLUMNS ( Tabelle1[Status], Tabelle1[Date], Tabelle1[MissionID] ) )[Measure 4] = COUNTROWS ( SUMMARIZECOLUMNS ( Tabelle1[Status], Tabelle1[Date] ) )Still not sure if this is the best option either.
- parry2k
Applicable88 yes, it is important to know what you are trying to achieve? How do you want to visualize the data? How the filter and slicer should have an impact on the calculation? Without knowing all this it is very hard to answer the question. More details with samples data you provide, you will get a more precise and quick answers.
- Applicable88
Hello parry2k , PaulDBrown , KNP ,
I finally finished some sample data. In the moment I'm dealing with machine data, so its not finance related.
With machine data I often times need to count Missions or rows. Here is an simplified excerpt:
here is my sample data: https://drive.google.com/drive/folders/1ZjzefXohslwuz5iXg99np8DOXChzUt4g?usp=sharing
Problem is that MissionID is not unique. There are many variations. So grouping also serves to group distinct combinations together. If the MissionID would be unique a simple distinctcount would tell me the amount of Missions. But I want to know even more from my data:
Total amount of Missions:
Measure = sumx(summarize(Tabelle1,Tabelle1[Status],Tabelle1[Date],"Amount",DISTINCTCOUNT(Tabelle1[MissionID])),[Amount])How many variations exist (MissionID's with different time and date etc.):Measure 2 = COUNTX(summarize(Tabelle1,Tabelle1[Status],Tabelle1[Date],"Amount",DISTINCTCOUNT(Tabelle1[MissionID])),[Amount])PaulDBrown already showed a similar approach with addcolumns in a previous post.I wrote a comment in the pbix-file. In case it is still difficult to understand, I hope the measures displayed in the card and table visuals speaks for themselves.If there is a better and more effective approach, I would be eager and happy to know. But in the moment I don't see using a simple sum or count to get to my values.Thanks for the effort so far and hope to hear from you soon.Best.
