Forum Discussion

flaviobdsti's avatar
flaviobdsti
Icon for Helper I rankHelper I
3 years ago
Solved

Create measures that need to consider grouped values

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:

CODESALES DATATOTAL
1201/25/2022100,000.00
101/25/202225,000.00
2102/10/202221,431.12
2303/15/202232,122.27
1403/15/202233,348.91
101/25/202227,612.10
103/15/20225005.11
203/15/202218,254.10
603/15/202214,254.10
404/21/202233,348.91
804/21/202218,254.10
1202/10/202218,254.10
504/21/202233,348.91
101/25/20229,211.11
603/15/20223000.23
202/10/202218,254.10
202/10/202214,348.91
1403/15/20227,976.01
1202/10/20226590.31



Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      flaviobdsti
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        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.