Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |