Append table kpi's

[Alex1960ad]

I have an appended table that has two year columns, 2022 and 2023, and nine rows of financial P&L data. I want compare 2023 with 2022 column and if the data is greater in 2023 then add a traffic light kpi. I have tried to use the "previous " FX calcuation using the field "year" in visual calc to put a CHAR1104 dot in the fourth column but it places this in a row at the bottom of the table  and cant get it to put the dot in  column so I can conditionally format it ? Help.

[Alex1960ad]

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.

[v-aatheeque]

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.

[Alex1960ad]

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 · Accepted solution]

Hi @Alex1960ad ,

What specific metrics or columns (e.g., TOTAL_REVENUE, TOTAL_EXPENSES, etc.) do you want to compare between the years?

[Alex1960ad]

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

[v-aatheeque]

Hi @Alex1960ad ,

Thanks for reaching out to the Microsoft Fabric Community forum.

@lbendlin , Thank you for the prompt response!

1.  First, ensure you have a column or measure to compare 2023 with 2022 data.

2. Use the following DAX to create the KPI column that includes the * symbol or any desired character:

KPI =

IF (

    Sheet1[2023] > Sheet1[2022],

    "@",  -- Displays the dot if 2023 > 2022

    "1"              -- Blank if condition is false

)

NOTE : Replace Table names, etc., with the actual table names, and with the relevant column names.

3. Apply Conditional Formatting :

• Select the Table visual.
• Click on the KPI column header dropdown >> Conditinal Formatiing >> Icons.
  
  The desired output after appending the tables is shown below:

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[Alex1960ad]

Thank  you Abdul for responding. I dont understand your first comment "First, ensure you have a column or measure to compare 2023 with 2022 data". As my example is a appended table made from two tables, the "Year" field column in the appended table is stacked on eacher other in the same column, so I dont see how I can compare one column with itself? I have Year column having 2500 "2023" entries then followed by 2500 "2020" directly underneath.

[v-aatheeque]

Hi @Alex1960ad ,

Let me clarify my earlier comment and provide detailed steps to help you compare 2023 data with 2022 data in your scenario:

1. Calculated Column for Year Comparison:
   In my scenario, I created a calculated column to compare data for 2023 with 2022. This allows you to create year-specific measures or calculations in your model.

2. How to Compare Years in a Single Column (Your Scenario):
   Since your "Year" column contains all years stacked within the same column (e.g., 2500 entries for 2023 followed by 2500 entries for 2022), you can follow these steps in Power BI to achieve the comparison:

   Steps:

   • Step 1: Load both tables into Power BI. Navigate to Transform Data to open Power Query.
   • Step 2: Use Append Queries:
     • If you want to create a new combined table, select Append Queries as New.
     • Alternatively, you can append one table into an existing table.
     • Select both tables to combine them.
   • Step 3: Once the tables are appended, click Close & Apply to load the combined data back into Power BI.

3. Comparing the Data:
   Once your data is appended into a single table, you can create measures in Power BI to compare values for 2023 and 2022. For instance, you can filter the data by year using DAX formulas to compute differences or comparisons between the two years.

I hope this resolves your query! If this solution works for you, kindly click Accept Answer and select Yes for "Was this answer helpful?" Feel free to let me know if you have further questions! 

[v-aatheeque]

Hi @Alex1960ad ,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Alex1960ad ,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum

[v-aatheeque]

 

Hi @Alex1960ad   ,
If your issue has been resolved, kindly click Accept Answer and mark Yes under "Was this answer helpful."

If you have any additional questions related to this thread, feel free to let us know, and we will do our best to assist you further.

For any new questions or issues, we kindly request you to create a new thread.

[lbendlin]

Better to unpivot your year columns, then you can use regular measures for the conditional formatting.