Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I am creating a table that shows some group up data using basically this format:
Customer | Article | Quantity | ProductiveCycle |
1234 | ABC | 20 | SFLSTN |
5678 | XYZ | 30 | SFEOFL |
This table is getting generated by using the data of all orders historically done for various articles.
The orders are not equal though, and so they have different ProductiveCycle values, in example:
Customer | Article | Quantity | ProductiveCycle |
1234 | ABC | 2 | SFLSTN |
1234 | ABC | 2 | SFLSTN |
1234 | ABC | 1 | SFLSTN |
1234 | ABC | 5 | SFLSTNFK |
1234 | ABC | 5 | SFLSTNFK |
1234 | ABC | 5 | SFLSTNFKFL |
Is there a way in the main table (a PowerBI matrix) to show the most common value from the database data, ie the one for which I've received more orders for that combination of customer and article (in this example SFLSTN, as it appears in 3 different orders) and/or the one for which I produced the highest total quantity summing up all orders for the same customer and article (in this example SFLSTNFK, as the total ordered quantity is 10, greater than the sum of ordered quantity for all other ProductionCycle values)?
I could do this in Oracle directly, but I'm trying not to do any pre-grouping of the order data so that I can get them easily from different sources.
Thanks a lot for your help, best regards,
Niky
Solved! Go to Solution.
Hi @NeuroNiky ,
Here are the steps you can follow:
1. Create calculated column.
Count_column = COUNTX(FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Article]=EARLIER('Table'[Article])&&'Table'[ProductiveCycle]=EARLIER('Table'[ProductiveCycle])),[ProductiveCycle])
2. Create measure.
Flag =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),[ProductiveCycle])
var _max=MAXX(ALL('Table'),[Count_column])
return
IF(
MAX('Table'[Count_column])=_max,1,0)
Sum_Measure =
SUMX(
FILTER(ALL('Table'), 'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),
[Quantity])
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank a lot! Works like a charm!
Hi @NeuroNiky ,
Here are the steps you can follow:
1. Create calculated column.
Count_column = COUNTX(FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Article]=EARLIER('Table'[Article])&&'Table'[ProductiveCycle]=EARLIER('Table'[ProductiveCycle])),[ProductiveCycle])
2. Create measure.
Flag =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),[ProductiveCycle])
var _max=MAXX(ALL('Table'),[Count_column])
return
IF(
MAX('Table'[Count_column])=_max,1,0)
Sum_Measure =
SUMX(
FILTER(ALL('Table'), 'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),
[Quantity])
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
127 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |