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.
Solved! Go to Solution.
This is a second post of this message...not sure where the first went. Please read this one.
Hi @icespedes ,
My pleasure!
In order to facilitate our conversation, would you please you the following syntax?
[measure name] = blah,blah,blah
tablename [Column name] = and so forth
This is common usage, and lets the other person know whether they are looking at a measure or a calculated column.
Thank you!
So, I took what you presented and applied it to the original data that you gave us as follows:
And
Proud to be a Super User!
Hi @Anonymous ,
If this solves your issue, please consider marking my reply as the solution.
Thanks,
Proud to be a Super User!
Hi @Nathaniel_C @Ashish_Mathur ,
It will not work since everything is in the same table. At the moment I have something like this:
What I need is simple, using these formulas:
In order to get this:
It worked on a small dataset but it didnt in a large one with these formulas:
Hi @Anonymous ,
Thinking about this a little further, the functions don't change based on rows, except for two possibilities that spring to mind.
One there is some bad data, or possibly unexpected data in your column. As the number that you reported
"It returns around 246,456. I assume it does a count per row and then a sum among rows." is very large, it is possible that there is a large number in your data set. You can sort that column in descending order, or do a number filter for greater than some expected limit as in the picture below. (I used 100, and the large number showed up in the table
The other possibility that I can think of is that dataset is too large for your system, if you are importing it. In which case it may be including a sum of the remaining rows. Don't know how that function works, though.
If this solves your problem, please consider marking this as a solution,
Proud to be a Super User!
Hi @Anonymous ,
Thank you for the additional information, it is always helpful! (You can click on the following pics to get a larger version.)
So, I created in Power BI the following table.
I also followed along by seeing what your example in Excel.
As you know, the average is the total / count. Thus in the Excel, we can either use Average(), or =16/12 (Sum of column/ by row count.
If you wish to duplicate this in PBI, you can use the following measure.
Proud to be a Super User!
Hi,
Really appreciate all your efforts however, as you said, this gives you the average by purchase order, not by customer as I requested.
Nonetheless, I figured it out. I did the following:
and then:
Hi @Anonymous ,
You are welcome!
So I have a question, a suggestion and an observation.
Does your solution work on your large dataset?
My suggestion is that you might use
Proud to be a Super User!
Hi @Nathaniel_C
Thanks for the info. Way faster, however, I dont know which number is correct.
At the moment, Im using:
This is a second post of this message...not sure where the first went. Please read this one.
Hi @icespedes ,
My pleasure!
In order to facilitate our conversation, would you please you the following syntax?
[measure name] = blah,blah,blah
tablename [Column name] = and so forth
This is common usage, and lets the other person know whether they are looking at a measure or a calculated column.
Thank you!
So, I took what you presented and applied it to the original data that you gave us as follows:
And
Proud to be a Super User!
Hi @Anonymous ,
My pleasure!
In order to facilitate our conversation, would you please use the following syntax?
[measure name] = blah,blah,blah
tablename [Column name] = and so forth
This is common usage, and lets the other person know whether they are looking at a measure or a calculated column.
Thank you!
So, I took what you presented and applied it to the original data that you gave us as follows:
And
Proud to be a Super User!
Hi,
You say that your solution worked on a small dataset but not on a large one. That's surprising. There is another solution you can try though i am not sure of whether it will work on a 10 million row dataset. You could select all columns other than the 3 columns (which you are referring to in your measure) and select "Unpivot other columns". Now you could use the Average function on the single numeric column.
Hi @Ashish_Mathur ,
This would convert my table in a more than 10MM row dataset, and will destroy all my other calculations. I dont think this would be the best way.
@Anonymous ,
How are you planning to use this? Will a measure work? Are you putting this on a dashboard? Or do you need a CColumn. Not sure, because you said "...or some other workaround?"
Do you have a products lookup table, a customer lookup table, and a sales data table?
Thanks,
Proud to be a Super User!
Hi @Nathaniel_C ,
I think the best way to do it would be creating a CColumn. What I need is the average amount of distinct products per person. This means that I need the amount of distinct products for each customer and then make an average.
@Anonymous when you say it doesn't work what happens?
Proud to be a Super User!
When I do it, it returns the same result for every row.
Does it return 3 for every row?
It returns around 246,456. I assume it does a count per row and then a sum among rows.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |