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,
I'm having a little bit of trouble getting a particular calculation into one of my Power BI queries and was hoping one of you may be able to help!
I have a table that lists the number of cars we have in a dealership month on month. The data looks something like this:
Dealer | Car | Jan | Feb | Mar |
Dealer ABC | Car Type 1 | 5 | 4 | 1 |
Dealer ABC | Car Type 2 | 4 | 9 | 3 |
Dealer ABC | Car Type 3 | 1 | 7 | 8 |
Dealer XYZ | Car Type 1 | 2 | 2 | 3 |
Dealer XYZ | Car Type 2 | 1 | 4 | 1 |
Dealer XYZ | Car Type 3 | 4 | 0 | 2 |
What I want to do is to find out the maximum for each dealer as a whole. If I enter a Max calculation into a new column and display it in a visual table in a report, I am getting a skewed figure as, in the example for Dealer ABC, it takes the 5 from car 1, 9 from car 2 and 8 from car 3 and gives me 22.
What I actually want to see is that for that dealer, the figures are Jan = 10, Feb = 20, Mar = 12, making the max 20.
I'm tying myself in knots trying to figure this out and I'm sure there's a simple way of doing it.....I just don't know what that is!
Solved! Go to Solution.
Hello @KatieH
At first, you need to transpose your table (you can use Power Query) like this:
Next, create measure something like this:
Max Sales Accross Month :=
MAXX (
ADDCOLUMNS (
SUMMARIZE ( 'Table'; 'Table'[Month] );
"MonthSales"; CALCULATE ( SUM ( 'Table'[Quantity] ) )
);
[MonthSales]
)
Hi @KatieH,
As explained by @popov, please unpivot your data first .
I created the measure but with a slighty different approach where my table is called Data, my fields named Dealer, Amount and Mois( for Month).
Feel free to check both solutions and let us know:
Final = CALCULATE ( MAXX ( SUMMARIZE ( Data, Data[Dealer], Data[Mois], "Sales", SUM(Data[Amount] ) ), [Sales] ), ALLEXCEPT(Data,Data[Dealer]) )
Ninter
Hello @KatieH
At first, you need to transpose your table (you can use Power Query) like this:
Next, create measure something like this:
Max Sales Accross Month :=
MAXX (
ADDCOLUMNS (
SUMMARIZE ( 'Table'; 'Table'[Month] );
"MonthSales"; CALCULATE ( SUM ( 'Table'[Quantity] ) )
);
[MonthSales]
)
Hi @KatieH,
As explained by @popov, please unpivot your data first .
I created the measure but with a slighty different approach where my table is called Data, my fields named Dealer, Amount and Mois( for Month).
Feel free to check both solutions and let us know:
Final = CALCULATE ( MAXX ( SUMMARIZE ( Data, Data[Dealer], Data[Mois], "Sales", SUM(Data[Amount] ) ), [Sales] ), ALLEXCEPT(Data,Data[Dealer]) )
Ninter
Thank you both so much! I tried both of these and they worked brilliantly!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |