Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a database with the financial results of several companies by quarters from 2018 to 2021, I have created the following measure in order to give a ranking to each company according to the indicator that is evaluated (for this example "Ventas" and " Activos"):
Everything was going well until I tried to calculate in the subtotals and grand total the average of the rankings or qualifications that each company has had.
For example, in the table below I selected a specific company to check the sub totals and grand totals:
As you can see, the formula is not calculating the averages of the rankings for each year and quarter. Do you know if there is a solution for this, in what part of the formula I am falling?
Thanks
Hi pfinancero:
I have proposed file for your solution but am not sure how to attach it. This is the data model and measures. I tried to use your figures where I could.
Hi @pfinanciero
Can you share a sample of your data in a text format or share your PBIX file?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @VahidDM @Whitewater100 thanks for your answer, let me give more info about the data.
I cant share the pbi file, then i will try to give a short description of the file.
All financial data comes from the following table called "Consolidado":
As you can see, the table is composed of the columns of dates, "Grupo" (company grouper), "Cuenta puc" (accounting account id), "Detalle puc" (accounting id description), "NIT" (company id) and "Valor ($ M)" (they are the values).
From the id of the ledger account and the value column, several indicators are calculated, including the value of sales "Ventas" and assets "Activos" per quarter for each year.
From those calculated indicators I created the following table using the SUMMARIZE formula, the name of this table is "Ratios":
As you can see, this table has an additional column with the name of the company (related from the NIT), the column "Indicador" which in this case are sales and assets, the column "Valor" which are the values of the indicator of each company per quarter. (Please ignore the "Prueba" column)
The ranking of companies by indicator is calculated from the Ratios table, taking into account the quarters of each year.
The formula that I used to calculate the ranking of each company by indicator works well:
Need: the problem I have is that I need (if possible) to calculate the average of the rankings in the subtotals and grand totals of the following table:
In the previous table I selected a particular company to see its ranking in the different quarters and year by indicator. As you can see in the rows of sub totals, grand totals and in the total column it is not calculating the average of the rankings obtained by the company. For example, in 2018 the average ranking should be 21 and not 10 for the "Activos" indicator, and for the "Ventas" indicator it should be 26 and not 15.
I think my problem is somewhere in this section of the formula but i couldn't find the error:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |