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.
Hi all.
I am trying to apply a minimum function after aggregation. Meaning that PBI should not look in raw data for the minimum, but instead it should find the minimum after aggregation.
My minimum function is conditional. It is about finding the "point 0" for a time series (paybacktime).
It would seem that this is a relative frequent occuring task, so wondering if someone have a nice recipe for this? 🙂
Best Regards,
Torben
Solved! Go to Solution.
@Anonymous , Does that mean you want to group data till a level and take min
minx(summarize(Table, Table[Group], "_1",sum(Table[Value])),[_1])
You can more than one group like Table[Group], Table[Group1] etc
Another way
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
@Anonymous , Does that mean you want to group data till a level and take min
minx(summarize(Table, Table[Group], "_1",sum(Table[Value])),[_1])
You can more than one group like Table[Group], Table[Group1] etc
Another way
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
Exactly! Except that the "summarize" part should be dynamic. We have lot's of users on an Excel-connection that will aggregate data in all kind of ways.
Btw: i haven't examined how these kind of functions works in an Excel-connection.
Hi @Anonymous ,
First go to query editor>select column 000,001,002,003>choose "unpivot columns";
Then create 2 measures as below:
_total = SUMX(FILTER(ALL('Table (2)'),'Table (2)'[Attribute]=MAX('Table (2)'[Attribute])&&'Table (2)'[Country]=MAX('Table'[Country])),'Table (2)'[Value])
payback periods =
var a=MINX(FILTER(ALL('Table (2)'),'Table (2)'[_total]>0),'Table (2)'[_total])
Return
CALCULATE(MAX('Table (2)'[Attribute]),FILTER('Table (2)','Table (2)'[_total]=a))
Finally you will see:
For the related .pbix file, pls click here.
Im getting an error message when opening the pbix file:
Im using may-2020 version of PBI.
Anyhow, I will try out your formulas provided 🙂
Hi @Anonymous
Try installing power bi again.
Hi @Anonymous
Not sure if understand the requirement, it would be a lot easier if you would provide a data sample, explain your scenario and provide an expected outcome.
but you can try to use MAXX aggregating over an attribute, like for example.
max sales by color = MAXX( VALUES( table[color] ), CALCULATE( SUM( table[sales amt] ) ) )
Thanks for your input.
Here is a visualization of my problem, hope it's helpful.
Top table: here is my raw data, split by country and device. The columns "000", "001", .. represent the accumulated customer value.
Middle table: a PBI user have aggregated the values on country, perhaps in a excel connection.
Bottom table: this is the output im looking for (it does not need to show the relative month, just the break-even month).
I tried the following: payback_month = CALCULATE(MIN(table[relative_month]),cashflow[value_acc] > 0).
This returns the minimum value, but for the raw/non-aggregated data!
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 |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |