Hi all, I guess this is pretty basic for most, but I cant seem to figure it our. I have an Excell file imported in Power BI. The value in a collumn is either "Yes", "No" or empty. Now I want to create the visual in which it gives the total sum of the "yes, another with the total sum of "No" and the last with the total number of empty. How do I do this?

Hi Reimer , You can create calculated columns as mentioned above. Another single work around is using visual filter. For example: And negi007 mentioned, you can use IF() function to create calculated column. Let us know if you need more help. Best Regards, Jay Community Support Team _ Jay Wang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Total sum based on values | Microsoft Fabric Community

8 Replies

ryan_mayu
Super User
6 years ago
Reimer

You can create three measures
numberofempty = COUNTAX(FILTER('Table','Table'[Column1]=""),'Table'[Column1])
numberofno = COUNTAX(FILTER('Table','Table'[Column1]="No"),'Table'[Column1])
numberofyes = COUNTAX(FILTER('Table','Table'[Column1]="Yes"),'Table'[Column1])
- Reimer
  Helper II
  6 years ago
  Almost!
  
  This is really helpfull, thank you. But I also Want to add a colum that say 1 if the row is empty, no or yes.
  
  Is this possible as well?
  - negi007
    Community Champion
    6 years ago
    ReimerYou can achieve this too. For this you will need to create a calculated column.
amitchandak
Super User
6 years ago
Reimer ,

Sum yes = calculate(countrows(Table),Table[columns]="Yes")
Sum No = calculate(countrows(Table),Table[columns]="No")
Sum Blank = calculate(countrows(Table),isblank(Table[columns]))
RichardJ
Responsive Resident
6 years ago
Hi Reimer
One method would be to 'Replace Values' of the null (empty) cells during the import.
Once you have the Excel data in the Power BI file, click on Transform Data

Then choose the 'Replace Values' option

and replace the 'null' values with something meaningful (e.g. 'No Decision' or you could use 'Blank' instead)

You can then create the charts/visuals as shown.

Hope this answers your question.

If you could let me know i'd appreciate it.

Cheers,
Richard
negi007
Community Champion
6 years ago
Reimer Here you will be required to create 3 calculated measures

1. to Calculated number of "Yes"
Yes_Count = CALCULATE(COUNTA('Yes/No'[Column]),'Yes/No'[Column]="Yes")

2. to Calculated number of "No"
No_Count = CALCULATE(COUNTA('Yes/No'[Column]),'Yes/No'[Column]="No")

3. to Calculated number of Empty records
Empty_Count = CALCULATE(COUNTA('Yes/No'[Column]),'Yes/No'[Column]="")
Anonymous
Not applicable
6 years ago
Hi Reimer ,

You can create calculated columns as mentioned above.

Another single work around is using visual filter.

For example:

And negi007 mentioned, you can use IF() function to create calculated column.

Let us know if you need more help.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.