SUM, MAX AND GROUP BY

[Anonymous]

Hello, 

I´m trying to create a new KPI in my CdM.

This CdM will be filtered by two slicers: Company and MonthID

I need that:

• When I select MonthID = 202001 and Cod_Company = 5 the KPI show 12.750 Customer1
• When I select MonthID = 202001 and Cod_Company = 1 the KPI show 1.000 Customer4
• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 5 the KPI show 12.750 Customer1
• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1 the KPI show 13.000 Customer4
• When I select MonthID = 202001 and Cod_Company = 1, 5 the KPI show 12.750 Customer1
• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1, 5 the KPI show 13.000 Customer4

[v-yuta-msft · Accepted solution]

@Anonymous ,

Modify the measure as below:

Measure 2 = 
VAR Sales =
    CALCULATE (
        MAX ( TopSalesCustomer[SALES] ),
        FILTER (
            TopSalesCustomer,
            TopSalesCustomer[MONTHID] = RELATED ( 'DATE'[MONTHID] )
                && TopSalesCustomer[COD_COMPANY] = RELATED ( COMPANYS[COD_COMPANY] )
        )
    )
VAR CustomerNo =
    CALCULATE (
        MAX ( TopSalesCustomer[COD_CUSTOMER] ),
        FILTER (
            TopSalesCustomer,
            TopSalesCustomer[MONTHID] = RELATED ( 'DATE'[MONTHID] )
                && TopSalesCustomer[COD_COMPANY] = RELATED ( COMPANYS[COD_COMPANY] )
                && TopSalesCustomer[SALES] = Sales
        )
    )
VAR CustomerName = CALCULATE(MAX(Customers[Name]), FILTER(Customers, Customers[COD_CUSTOMER] = CustomerNo))
RETURN
    CONCATENATE ( FORMAT ( Sales / 1000, "0.000" ), CONCATENATE ( " ", CustomerName ) )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft,

The measure now is correct for the firsts points, but should solve also the next points:

• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1 the KPI show 13.000 (1000+2000+3000+7000) Customer4
• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1, 5 the KPI show 13.000 (1000+2000+3000+7000) Customer4

If you select more months where the max custormer is for example the customer4, the sales for this months is the sum

You also can select two companys

Thanks!!

[v-yuta-msft]

@Anonymous ,

---

The measure now is correct for the firsts points, but should solve also the next points:

• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1 the KPI show 13.000 (1000+2000+3000+7000) Customer4
• When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1, 5 the KPI show 13.000 (1000+2000+3000+7000) Customer4

---

Could you please also explain this logic?

Regards,

Jimmy Tao

[v-yuta-msft]

@Anonymous ,

You can create a measure like below, please notice that I have only add the first condition in your requirement, please fulfill all the conditions in the switch statement. Please also check the attached pbix file.

Measure = 
VAR Temp =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                TopSalesCustomer,
                TopSalesCustomer[MONTHID] IN VALUES ( 'DATE'[MONTHID] )
                    && TopSalesCustomer[COD_COMPANY] IN VALUES ( COMPANYS[COD_COMPANY] )
            ),
            "MONTHID", TopSalesCustomer[MONTHID]
        )
    )
VAR Cod_Company =
    SELECTEDVALUE ( COMPANYS[COD_COMPANY] )
RETURN
    SWITCH (
        TRUE (),
        Temp IN { 202001 }
            && Cod_Company = 5, CONCATENATE (
            FORMAT (
                CALCULATE (
                    MAX ( TopSalesCustomer[SALES] ) / 1000,
                    FILTER (
                        ALL ( TopSalesCustomer ),
                        TopSalesCustomer[MONTHID] = 202001
                            && TopSalesCustomer[COD_COMPANY] = 1
                    )
                ),
                "0.000 "
            ),
            CONCATENATE (
                "Customer",
                CALCULATE (
                    MAX ( TopSalesCustomer[COD_CUSTOMER] ),
                    FILTER (
                        ALL ( TopSalesCustomer ),
                        TopSalesCustomer[MONTHID] = 202001
                            && TopSalesCustomer[COD_COMPANY] = 1
                    )
                )
            )
        )
    )

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft 

This PBIX not resolve my problem, because the first condition is

• When I select MonthID = 202001 and Cod_Company = 5 the KPI show 12.750 Customer1

The pbix should be dynamic, because in the future will be more MonthID, Companys....
This is a simple example to create the measures.

It´s not necessary that the value and the name of company is in the same measure.

Thanks a lot

[v-yuta-msft]

@Anonymous ,

Sorry for mistake, modify the measure as below:

Measure = 
VAR Temp =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                TopSalesCustomer,
                TopSalesCustomer[MONTHID] IN VALUES ( 'DATE'[MONTHID] )
                    && TopSalesCustomer[COD_COMPANY] IN VALUES ( COMPANYS[COD_COMPANY] )
            ),
            "MONTHID", TopSalesCustomer[MONTHID]
        )
    )
VAR Cod_Company =
    SELECTEDVALUE ( COMPANYS[COD_COMPANY] )
RETURN
    SWITCH (
        TRUE (),
        Temp IN { 202001 }
            && Cod_Company = 5, CONCATENATE (
            FORMAT (
                CALCULATE (
                    MAX ( TopSalesCustomer[SALES] ) / 1000,
                    FILTER (
                        ALL ( TopSalesCustomer ),
                        TopSalesCustomer[MONTHID] = 202001
                            && TopSalesCustomer[COD_CUSTOMER] = 1
                    )
                ),
                "0.000 "
            ),
            CONCATENATE (
                "Customer",
                CALCULATE (
                    MAX ( TopSalesCustomer[COD_CUSTOMER] ),
                    FILTER (
                        ALL ( TopSalesCustomer ),
                        TopSalesCustomer[MONTHID] = 202001
                            && TopSalesCustomer[COD_CUSTOMER] = 1
                    )
                )
            )
        )
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, @v-yuta-msft 

Now this measurement is correct but it doesn't solve my problem.

This measure must be dynamic because there may be more mesid and companies in the future

I need to indicate who is the client with the most sales in the selected period and the company and also indicate the sum of the sales. For example, if you select a month and a company, the kpi displays the value of sales for this company and the customer.

The problem is when you select more months, because I need the customer with the most sales in the selected periods.

Thank you

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.