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?
Hi Abdul,
Thanks for your answer. Above is my tables. I have appended the FIDS2022Shrt ( this where the organisational data is for that year (with the organisational name the row and the column the year and other columns Total Revenue etc. ) and FIDS2023Shrt table into the table called Append1. From your reponses I dont understand your first response
KPI =
IF (
Sheet1[2023] > Sheet1[2022],
"@", -- Displays the dot if 2023 > 2022
"1" -- Blank if condition is false
)
What is the the sheet 1 you refer to ? I have only the tables as listed I dont have any "sheets" Are you saying the Sheet1 is the table name ? I tried this. I see from your example shown you have been successful but it is remaining frustratingly elusive for me - Im new to dax.
Hi ,
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!
Hi Alex1960ad ,
What specific metrics or columns (e.g., TOTAL_REVENUE, TOTAL_EXPENSES, etc.) do you want to compare between the years?