Calculated column creating duplicates under all categories

Question

I am trying to put a count of projects by Billable RAG Status (Green,Amber,Red) in a column chart but the way my data is structured, the calculated column formula I have is showing a single ID under all RAG status categories instead of just under one.

The formula for the calculated column is: (yes I am referencing measures in this as well as the values do not exist in the original data source)

BillableRAGStatus = IF(CALCULATE([BillableActual%]>1),"Red",IF(CALCULATE([EAC%Billable]<=1),"Green",IF(CALCULATE([BillableActual%]<1 || [EAC%Billable]>=1),"Amber")))

I've attached screenshots as well. PS I cannot change the structure of my data and I need to use a calculated column as a measure does not allow me to have the column chart as clickable.

ajohnso2 · Answer

Please provide a sample pbix and your expected outcome

rupak_bi · Answer

Hi PaulinaJ&nbsp;,To diagnose the issue, you may create two calculated column of two different measures first to check what value you are getting. Then create the third column to flag it. Hope this will help. Please share sample data for better solution.

angith_nair · Answer

Hi&nbsp;PaulinaJ&nbsp;&nbsp;Replace your BillableRAGStatus calculated column with a measure that evaluates the RAG status for each project.BillableRAGStatus = 
    SWITCH(
        TRUE(),
        [BillableActual%] &gt; 1, "Red",
        [EAC%Billable] &lt;= 1, "Green",
        [BillableActual%] &lt; 1 || [EAC%Billable] &gt;= 1, "Amber",
        "No Status"
    )Create another measure to count the number of projects for each RAG status.CountProjectsByRAG = 
    COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Red"
        )
    ) + COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Amber"
        )
    ) + COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Green"
        )
    )Add BillableRAGStatus as the category for your column chart, and use CountProjectsByRAG as the value. This will count and categorize projects correctly based on the dynamically calculated RAG status.&nbsp;

paulinaj · Answer

How can I change the second measure to look at distinct count of rows? My raw data could have 300 rows for the same ID but I just want it to look at distinct row.CountProjectsByRAG = 
    COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Red"
        )
    ) + COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Amber"
        )
    ) + COUNTROWS(
        FILTER(
            YourTable,
            [BillableRAGStatus] = "Green"
        )
    )&nbsp;Also, I am not able to add BillableRAGStatus as the category of the column chart, it is only allowing me to add it to tooltips

angith_nair · Answer

Use the logic for&nbsp;BillableRAGStatus in a calculated column and then you can add this column into the axis of the column chart.&nbsp;Also for&nbsp;CountProjectsByRAG, use DISTINCTCOUNT instead of COUNTROWS.

Forum Discussion

Calculated column creating duplicates under all categories

6 Replies

Related content

Duplicate ranking

Calculate Average per category

calculate max per category.

Calculating cumulative total across categories

remove duplicate value within a row