Forum Discussion

MPG1907's avatar
MPG1907
New Member
2 years ago
Solved

undefined

Hi I am new to Power BI. I need to analyse data about language classes attendance. I need to analyse how many people attended each class each month, their nationality and gender for last 5 years. Do ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hola MPG1907. Recomiendo que crees dos consultas adicionales.

    La primera, una consulta que sea Class code, para poder relacionar con Class Sub-Code, debes quitarle los numeros a partir del guion medio.

     

    Y la segunda tabla, es la tabla calendario. Con este formula DAX la puedes crear:

     

    TablaCalendario =
    VAR T_Calendario =
        ADDCOLUMNS(
            CALENDARAUTO(),
            "Año", YEAR([Date]),
            "Semestre", "Semes. " & CEILING(MONTH([Date])/6, 1),
            "Cuatrimestre", "Cuatr. " & CEILING(MONTH([Date])/4, 1),
            "Trimestre", "Trim. " & CEILING(MONTH([Date])/3, 1),
            "Bimestre", "Bim. " & CEILING(MONTH([Date])/2, 1),
            "Mes Número", MONTH([Date]),
            "Mes Nombre", FORMAT([Date], "MMMM"),
            "Semana", "Sem. " & WEEKNUM([Date], 2),  -- El número 2 indica que la semana comienza en lunes
            "Día Número", WEEKDAY([Date],2),
            "Día Nombre", FORMAT([Date], "dddd")
        )
    RETURN
        T_Calendario
     
    Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MPG1907 ,

     

    First of all, thank you andrezmar and qqqqqwwwweeerrr for the quick reply. 

    I have some other ideas for you to consider. Based on your test data, I think a pie chart would be a good option for you. You can visualize the various percentages.

    If you want to display the most popular Activity name as a visual object of the card then you can create measures.

     

    Measure = COUNT('classes delivered in 2023'[Activity name])
    Measure 2 = 
var _table=SUMMARIZE(ALL('classes delivered in 2023'),[Activity name],"count",[Measure])
var _max= MAXX(_table,[count])
RETURN MAXX(FILTER(_table,[count]=_max),[Activity name])

     

     

    Best Regards,

    Neeko Tang

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

qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Solution Sage
2 years ago

Hi MPG1907 

 

Can you share some sample data along with expected output

 

Regards

 

  • MPG1907's avatar
    MPG1907
    New Member
    2 years ago

    Hi,

    Thank you for your reply.

    Here is sample data: 

    1. classes with codes

    Class codeActivity name Course fee in $ ConsultantsLive/virtual
    LC-2438English $                     50,00XYLive Classroom Instruction
    LC-6323French $                     50,00XY1Live Classroom Instruction
    LC-7218German $                     50,00XY3Live Classroom Instruction
    LC-7266Spanish $                     50,00XY4Live Classroom Instruction
    LC-7058Italian $                     50,00XY4Live Classroom Instruction
    LC-6261Chinese $                     50,00XY6Virtual Classroom Instruction
    LC-6788Arabic $                     50,00XY7Virtual Classroom Instruction
    LC-7267Russian $                     50,00XY8Virtual Classroom Instruction
    LC-6330Portugese $                     50,00XY9Virtual Classroom Instruction
    LC-6677Dutch $                     50,00XY3Virtual Classroom Instruction

    2. classes delivered in 2023

    Activity nameClass  Sub-CodeTypeStart DateEnrolledMaleFemale
    EnglishLC-2438-19Live Classroom Instruction19.09.202315105
    EnglishLC-2438-13Live Classroom Instruction25.01.202320812
    EnglishLC-2438-14Live Classroom Instruction1.10.202319910
    EnglishLC-2438-15Live Classroom Instruction1.11.2023624
    EnglishLC-2438-16Live Classroom Instruction13.02.2023734
    EnglishLC-2438-17Live Classroom Instruction27.06.20231688
    EnglishLC-2438-18Live Classroom Instruction9.12.202319910
    EnglishLC-6966-20Live Classroom Instruction30.10.2023322012
    FrenchLC-6323-12Live Classroom Instruction24.03.202315510
    FrenchLC-6323-13Live Classroom Instruction22.06.20231899
    FrenchLC-6323-14Live Classroom Instruction11.08.2023231112
    GermanLC-7218-8Live Classroom Instruction3.02.2023361620
    GermanLC-7218-9Live Classroom Instruction21.09.202319811
    SpanishL-7266-10Live Classroom Instruction4.06.20231266
    SpanishLC-7266-11Live Classroom Instruction9.09.20231468
    SpanishLC-7266-12Live Classroom Instruction3.11.2023231112
    ItalianLC-7058-1Live Classroom Instruction28.04.202320911
    ItalianLC-7058-2Live Classroom Instruction10.06.20231596
    ItalianLC-7058-3Live Classroom Instruction28.10.20231367
    ChineseLC-6261-22Virtual Classroom Instruction14.06.20231376
    ChineseLC-6261-23Virtual Classroom Instruction19.09.2023201010
    ChineseLC-6261-24Virtual Classroom Instruction13.11.20231275
    ArabicLC-6788-7Virtual Classroom Instruction4.06.2023945
    ArabicLC-6788-8Virtual Classroom Instruction19.09.20231055
    ArabicLC-6788-9Virtual Classroom Instruction23.10.20231275
    ArabicLC-6788-10Virtual Classroom Instruction24.11.20231257
    RussianLC-7058-11Virtual Classroom Instruction24.03.2023734
    RussianLC-7058-12Virtual Classroom Instruction22.06.2023835
    PortugeseLC-6330-4Virtual Classroom Instruction9.09.2023514
    DutchLC-6677-2Virtual Classroom Instruction3.02.2023633

    I have same date for other years. 

    I am interested to see what course were most popular, gender distribution, which format was most popular, which consultant was engaged the most, etc.. 

    Thanks in advance. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hola MPG1907. Recomiendo que crees dos consultas adicionales.

      La primera, una consulta que sea Class code, para poder relacionar con Class Sub-Code, debes quitarle los numeros a partir del guion medio.

       

      Y la segunda tabla, es la tabla calendario. Con este formula DAX la puedes crear:

       

      TablaCalendario =
      VAR T_Calendario =
          ADDCOLUMNS(
              CALENDARAUTO(),
              "Año", YEAR([Date]),
              "Semestre", "Semes. " & CEILING(MONTH([Date])/6, 1),
              "Cuatrimestre", "Cuatr. " & CEILING(MONTH([Date])/4, 1),
              "Trimestre", "Trim. " & CEILING(MONTH([Date])/3, 1),
              "Bimestre", "Bim. " & CEILING(MONTH([Date])/2, 1),
              "Mes Número", MONTH([Date]),
              "Mes Nombre", FORMAT([Date], "MMMM"),
              "Semana", "Sem. " & WEEKNUM([Date], 2),  -- El número 2 indica que la semana comienza en lunes
              "Día Número", WEEKDAY([Date],2),
              "Día Nombre", FORMAT([Date], "dddd")
          )
      RETURN
          T_Calendario
       
      Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.