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.