Forum Discussion

KatieH's avatar
KatieH
Advocate IV
8 years ago
Solved

Summed Max Across Columns

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:

DealerCarJanFebMar
Dealer ABCCar Type 1541
Dealer ABCCar Type 2493
Dealer ABCCar Type 3178
Dealer XYZCar Type 1223
Dealer XYZCar Type 2141
Dealer XYZCar Type 3402

 

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!

 

Need Help
  • popov's avatar
    popov
    8 years ago

    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]
    )

     

  • Interkoubess's avatar
    Interkoubess
    8 years ago

    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

3 Replies

  • popov's avatar
    popov
    Resolver III
    8 years ago

    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]
    )

     

    • Interkoubess's avatar
      Interkoubess
      Solution Sage
      8 years ago

      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

      • KatieH's avatar
        KatieH
        Advocate IV
        8 years ago

        Thank you both so much! I tried both of these and they worked brilliantly!