Create measures that need to consider grouped values

[flaviobdsti]

Hello guys! I hope everyone is okay!
I have the problem below that I haven't been able to solve so far, I need to create two measures to add each one to a card. But the challenge is that in "Measure 1" I need to group the Code column, then add the grouped value and finally I need to count how many CODIGOS had sales greater than 8,000.00 according to the month selected in my calendar.

And in "Measure 2" still considering the grouping mentioned above, I need to identify the five CODIGOS with the highest values (Total) according to the month selected in my calendar.

Can you help me please?


MEASURE 1: I tried to solve this way but the values are not matching

Measure 1 = 

calculate(
    COUNTROWS(
        VALUES(Table[Code])
    ),
    FILTER(
        Table,Table[total]>=8000
    )
)

MEASURE 2: I tried using the TOPN formula, but I couldn't get the correct result



Example of my TABLE:

CODE	SALES DATA	TOTAL
12	01/25/2022	100,000.00
1	01/25/2022	25,000.00
21	02/10/2022	21,431.12
23	03/15/2022	32,122.27
14	03/15/2022	33,348.91
1	01/25/2022	27,612.10
1	03/15/2022	5005.11
2	03/15/2022	18,254.10
6	03/15/2022	14,254.10
4	04/21/2022	33,348.91
8	04/21/2022	18,254.10
12	02/10/2022	18,254.10
5	04/21/2022	33,348.91
1	01/25/2022	9,211.11
6	03/15/2022	3000.23
2	02/10/2022	18,254.10
2	02/10/2022	14,348.91
14	03/15/2022	7,976.01
12	02/10/2022	6590.31

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Sales measure: = 
SUM( Sales[Sales] )

Expected result 1: = 
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( Code[CODE], "@Sales", SUM ( Sales[Sales] ) ),
        [@Sales] > 8000
    )
)

top 5 sales CODE: =
VAR _salesbycodetable =
    SUMMARIZECOLUMNS ( Code[CODE], "@Sales", SUM ( Sales[Sales] ) )
VAR _topfive =
    TOPN ( 5, _salesbycodetable, [@Sales], DESC )
RETURN
    CONCATENATEX ( _topfive, Code[CODE], ", " )

[flaviobdsti]

Hi Jihwan Kim,
first thanks for the help, but unfortunately it didn't work in my model. I followed your advice about the measurements, but when I applied it to my model (.pbix), the error below occurred. Do you know what could have gone wrong?

[Jihwan_Kim]

Hi,

Thank you for your message, and please share your sample pbix file's link (onedirve, googledrive, ... ), and then I can try to look into it to come up with a solution that suits your data model.

Thanks.