Conditional Sum Between Two Tables PowerBI Desktop

Question

I'm a PowerBI newbie and struggling with the following.

I am providing stats on a Project and want to create a progress gauge which shows the value of each task, which is either done or not done. I have my data in a table called Issues:

This table is linked to an external data source so the values and calculations need to be dynamic. I want to create a second table which gives the total Effort, tasks which are DONE and tasks which are NOT DONE for each Form. Pivot Table, basically. For example -

Im not sure if I need to have those values expressed as numbers or percentages to have them work on a gauge for my dashboard.

So far, I have managed to summarise the form names in the second table, like this

Stats = SUMMARIZECOLUMNs(Issues[Form Name])

I am stuck on writing an expression (column or measure?) to get the rest of the data. I guess I need to compare the Form Names between the tables and add up the values according to a conditional. I have been playing with CALCULATE and SUMX but can't seem to get the conditional working.

Can someone please advise how I might accomplish this?

PS - I think I need to use SUMMARIZE to get the data but I dont see how to compare the Form Name column in the second table against the Form Name column in the first table. I type in the name and it doesnt give me the option from the dropdown.

Answer 1

I think you are overcomplicating. If your data source is a excel file or non-cube data (like Analysis Services) you could just create 3 calculated columns for each effort class (i.e. 'Done', 'Not done', 'Total') and then use this results to create the gauge.

Power BI table with added columns The calculated columns'DAX is:

    Done = IF(Issues[Status]="DONE",Issues[Effort],0)
    Not Done = if(Issues[Status]<>"DONE",Issues[Effort],0)
    Total Effort = Issues[Done]+ Issues[Not Done]

The gauge would look like this: Table visual and gauges

With Value = Done and Maximum Value = Total Effort for the blue one.