Forum Discussion
Append table kpi's
Hi Alex1960ad ,
What specific metrics or columns (e.g., TOTAL_REVENUE, TOTAL_EXPENSES, etc.) do you want to compare between the years?
Community SupportHi ,
Thank you for sharing the details and clarifying your setup. I understand your confusion—let me explain step-by-step and adjust the example to fit your scenario.
Clarification on "Sheet1"
In the previous example, "Sheet1" was used as a name for the table name. In your case, you should use the actual table name, such as Append1, where you have combined the data from FIDS2022Shrt and FIDS2023Shrt.
The DAX formula shared earlier assumes that your table has columns for both 2023 and 2022 data (e.g., 2023, 2022). If these columns exist in your Append1 table, you can directly compare them.
If your table structure is correct, you can use a DAX formula to create a calculated column.
In Power BI, go to the Modeling tab and click on New Column.
Use the following DAX formula to create your KPI:
DAX
Copy code
KPI = IF ( Append1[2023] > Append1[2022], "@", -- Displays "@" if 2023 is greater than 2022 "1" -- Displays "1" if 2023 is not greater than 2022 )
Replace Append1 with the actual name of your table if different.
This formula will evaluate each row in the Append1 table. If the value for 2023 is greater than 2022, it will display @. Otherwise, it will display 1.
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you.
Hi Abdul,
Really appreciate your help, I am reasonably competent in XL but not in power bi. Here is a sample of my append1 table where both years of data is in the one column stacked as a function of the append.
I dont have two separate date columns for 2022 and 2023 and thus your suggested "IF" statement assumption can't be performed with the layout of the attached table.
"The DAX formula shared earlier assumes that your table has columns for both 2023 and 2022 data (e.g., 2023, 2022). If these columns exist in your Append1 table, you can directly compare them."
I dont have this only in my append table I have only one YEAR column.
Your previous visualisation is what Im trying to achevie but remains tantilisingly close.
Firstly, can you acheive your visualisation with my current stacked Append1 table - with the aggregated year data in a single column? Or do I need to re-format?
Secondly, if yes - we can use in current format, please can you explain the single steps to achieve the visualisation (with the KPI indicators)?
Thirdly, note you have 'Sum of' column headers for the year data in your visualisaion example. Please can you explain these?
Thank you so much!
- v-aatheeque
Hi Alex1960ad ,
What specific metrics or columns (e.g., TOTAL_REVENUE, TOTAL_EXPENSES, etc.) do you want to compare between the years?All columns please, if however it is having to have 9 vlookups, I can have just two - net operating surplus and working capital. Again, thanks so much for your help.
- v-aatheeque
Hi Alex1960ad
Apologies for the issue you are facing.
Based on the shared screenshot, it seems the structure of the table differs from the initial assumption. In the latest screenshot, the YEAR values are stored in the same column rather than having separate columns for each year. This makes it unclear which specific column we should use for comparison. Could you confirm how the YEAR values relate to the metrics (e.g., NET_OPERATING_SURPLUS, WORKING_CAPITAL) and whether these metrics are aggregated or split across rows for each year? This clarification will help us finalize the approach to comparing the data effectively.Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum - Microsoft Fabric Community