Bar Chart for Costs that match Slicer selections

[RockandGrohl]

Hi, bit of a confusing one here but I'll give it my best.

Our Database of Projects has between 1-5 Stages per project, and each stage has multiple rows which are the individual cost components of the project.

Some projects in the database have stage 1, 3, 4, some have stages 3 and 4 only, some have 1 and 5 only, you get the drift..

What I'm trying to do is create a bar chart where I can use a slicer to select, say, stages 1, 3 and 4 and it shows three bars with the total cost for each, which is a sum only of projects that have stages 1, 3 and 4 in the database.


ChatGPT seems to think this is perfectly possible but the end result is that the chart it shows me doesn't appear to be summing up the costs only for projects that have all the selected gateways, see below:

Step 1: Create a Measure to Verify Valid Projects

ValidProjects =
VAR SelectedStages = VALUES('Database'[Stage])
VAR ProjectStages =
    CALCULATETABLE(
        VALUES('Database'[Stage]),
        ALLEXCEPT('Database', 'Database'[Project ID])
    )
RETURN
IF(
    COUNTROWS(SelectedStages) = COUNTROWS(INTERSECT(SelectedStages, ProjectStages)),
    1,
    0
)

Step 2: Create a Measure for Filtered Costs

FilteredCost =CALCULATE(    SUM('Database'[Cost (Original)]),    FILTER(        'Database',        [ValidProjects] = 1    ))

Step 3: Build the Bar Chart​

1. Add a Bar Chart to your report.
2. Set up the fields as follows:
   • Y-axis: Stage (from your Database table).
   • X-axis: FilteredCost (the measure we just created).
3. Add a Slicer:
   • Drag Stage to the slicer visual.
   • Enable multi-select mode in the slicer.

This just results in a chart which doesn't seem to only count projects that fulfill all of the Stage selection criteria.

 

[RoryA]

I'm not sure, but I think you'll need a disconnected table with the stages in it, that you can apply the slicer to. Otherwise, for any given row you want to ignore the stage filter for that row to calculate all the stages for the project but you need to compare that to all the selected stages, so you can't remove the filter.

 

[RockandGrohl]

I'm not sure, but I think you'll need a disconnected table with the stages in it, that you can apply the slicer to. Otherwise, for any given row you want to ignore the stage filter for that row to calculate all the stages for the project but you need to compare that to all the selected stages, so you can't remove the filter.

So this is the way I did it in Excel.

Create a table with Unique Project ID & Stage, then add a SUMIF for the Total Cost so we have one line for each Project ID & Stage with the cost next to it.

Create another table with just Unique Project ID on it and then 5 columns, one for each stage, and an additional 5 columns, one for each cost.


The stage column is just a countif, so 0 if that Project ID doesn't have a stage 1, or a stage 3 etc and 1 if it does. So if a Project ID has Stage 1, 3 and 4 then the column results would be 10110. So I can filter these stage count columns to 1 in the first column, 1 in the 3rd and 1 in the 4th and that shows me seven projects that have a stage 1, 3 and 4. I can get the costs that way and it shows me what I expect (That as a project progresses, the cost goes up from estimation to completion)


I want to bring that logic into Power BI and I'm so nearly there but stuck on the last bit.



Got it working! Here's how!

Database table which is structured as above
ProjectSummary table which is a summarized table with one row per Project ID & Stage. So a Project ID with 3 Stages has 3 rows, and each has a cost.

I used a measure to read the slicer selections and print a Binary code:

SelectedBinary =

VAR Stage1 = IF(CONTAINS(VALUES('Database'[Stage]), 'Database'[Stage], "S1"), "1", "0")
VAR Stage2 = IF(CONTAINS(VALUES('Database'[Stage]), 'Database'[Stage], "S2"), "1", "0")
VAR Stage3 = IF(CONTAINS(VALUES('Database'[Stage]), 'Database'[Stage], "S3"), "1", "0")
VAR Stage4 = IF(CONTAINS(VALUES('Database'[Stage]), 'Database'[Stage], "S4"), "1", "0")
VAR Stage5 = IF(CONTAINS(VALUES('Database'[Stage]), 'Database'[Stage], "S5"), "1", "0")
RETURN Stage1 & Stage2 & Stage3 & Stage4 & Stage5

So if I select Stage 1, 3 and 4 it returns 10110

On the Project Summary table I measured all the stages a Project ID has and printed into a column the binary code.

StageBinaryCode =

CONCATENATE(
CONCATENATE(
CONCATENATE(
CONCATENATE(
IF(CALCULATE(COUNTROWS('Database'), 'Database'[Stage] = "S1") > 0, "1", "0"),
IF(CALCULATE(COUNTROWS('Database'), 'Database'[Stage] = "S2") > 0, "1", "0")
),
IF(CALCULATE(COUNTROWS('Database'), 'Database'[Stage] = "S3") > 0, "1", "0")
),
IF(CALCULATE(COUNTROWS('Database'), 'Database'[Stage] = "S4") > 0, "1", "0")
),
IF(CALCULATE(COUNTROWS('Database'), 'Database'[Stage] = "S5") > 0, "1", "0")
)

I've then declared the binary output from the slicer Stage selection as a VAR and used it in a measure:

GWBinaryCost = 
VAR SelectedBinary = [SelectedBinary]  -- This captures the selected binary string
RETURN 
    CALCULATE(
        SUM(ProjectSummary[Total Cost (Original)]),
        ProjectSummary[StageBinaryCode] = SelectedBinary
    )

Crucially, I have to make sure that the Stage slicer is not slicing the ProjectSummary table because it results in a double-filter which scrubs all the results.



Brilliantly, this works!

Now I just need to change it because when I want to compare costs between Stage 3 & 4, it only shows me 00110 projects, but I still want to see projects that are 10110 or 11110 or 00111 etc. I have an idea on that, to change the numbers to ABCDE and then filter to where the column contains A&E, or C&D&E etc.

 

[RoryA]

That sounds a lot more complicated than what I was thinking of, but hey, if it works, who cares?

 

[RockandGrohl]

That sounds a lot more complicated than what I was thinking of, but hey, if it works, who cares?

Hahaha does it? To me it looks very simple when you see it up and running. Just trying to get the ABC thing working now.