Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm new to Power BI and I'd be very glad if you guys could help me solve the following problem.
I have to show the following data in a Matrix, but for it to make sense every row context have to be evaluated differently.
I've come up with this formula, but I have no idea how to make it work for the "Pendente" row.
MatrixRowMeasure =
VAR _Dummy = 0
RETURN
IF (SELECTEDVALUE('Tabela 2'[Indicadores]) IN { "Cancelado", "Concluído" },
// Here, I'm using the inactivate date relationship and I just want the "Projeto" that are "Cancelado" or "Concluído"
CALCULATE(COUNTROWS('Tabela'), USERELATIONSHIP(Data[Date], 'Tabela'[Data Finalização]), 'Tabela'[Motivo] = "Projeto"),
IF( SELECTEDVALUE('Tabela 2'[Indicadores]) = "Projeto",
// For "Projeto", just count rows using the active date relationship, which is 'Tabela'[Data de Criação]
CALCULATE(COUNTROWS('Tabela')),
// The measure I need to make which will accumulate the calculations above from both IF paths with those same calculations but from previous months
_Dummy
)
)
Basically, for the _Dummy part (the "Pendente" row) to make sense, it would have to get an initial value coming from another Measure and sum it with a calculation for the current context and I can get it to work for the first context of "Janeiro", but I also need the subsequent months to get the cumulative values of "Pendente" until that current month. For example, in the context of the month of "Fevereiro", the measure would return the calculation for the current month plus the result from the calculation of the month of "Janeiro", and so on for the following months.
Here is the expected result:
Indicadores Janeiro Fevereiro Março Abril Maio
|
And the calculation for the first value of "Pendente" is
And then uses the value from the previous month of "Pendente" for the next calculations.
@Linkk Is your data laid out exactly like shown in the matrix and in Excel? If so, you're doing it wrong.
Power Query unpivot your months into columns and make them actual date values. Any date in a month will work. second, date table! Google the importance of this!
You do those two things and this should be fairly easy with a basic SUM() with category down the side and Month Name (from the date table) across the top.
Thanks for you attention @littlemojopuppy! I'm actually using a data table, see:
and the matrix is laid out the way you mentioned, but I didn't mention 'Tabela 2'[Indicadores] is a Table with distinct values of 'Tabela 1'[Indicadores], that's why i'm counting the rows in 'Tabela 1':
I'm using a slicer for the selection of the year, which is considering just the year of 2023 for the images above.
Although I've seen some posts tackling cumulative measure, I wasn't able to translate them for my task at hand.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |