Hello, I´m trying to create a new KPI in my CdM.This CdM will be filtered by two slicers: Company and MonthIDI need that:When I select MonthID = 202001 and Cod_Company = 5 the KPI show 12.750 Customer1When I select MonthID = 202001 and Cod_Company = 1 the KPI show 1.000 Customer4When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 5 the KPI show 12.750 Customer1When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1 the KPI show 13.000 Customer4When I select MonthID = 202001 and Cod_Company = 1, 5 the KPI show 12.750 Customer1When I select MonthID = 202001,202002,202003,202004 and Cod_Company = 1, 5 the KPI show 13.000 Customer4

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

Not applicable

6 years ago

Solved

SUM, MAX AND GROUP BY

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

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.

SUM, MAX AND GROUP BY.pbix53 KB

8 Replies

amitchandak
Super User
6 years ago
The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

v-yuta-msft

Community Support

6 years ago

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.

SUM, MAX AND GROUP BY.pbix45 KB

Anonymous

Not applicable

6 years ago

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

Community Support

6 years ago

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.

v-yuta-msft
Community Support
6 years ago
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.
SUM, MAX AND GROUP BY.pbix53 KB
- Anonymous
  Not applicable
  6 years ago
  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
    Community Support
    6 years ago
    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

Forum Discussion

SUM, MAX AND GROUP BY

8 Replies

Related content

Running Sum Max Date

MAX date by group

DAX - max of group sums

Calculate Sum MAX

GROUP BY WITH SUM