cancel
Showing results for
Did you mean:
Regular Visitor

## How to calculate blank rows from calculated measure by summarize other columns

Hello All,

I am trying to do the following thing but no luck.

Here, I need to calculate the total no of stores available in Banner(3rd Col), but need to apply a filter there, the Total Loss Amt(4th Col) should not be blank.

The No of stores is calculated as Calculate(count([StoreID]), All(store[StoreID]))

(I have used ALL because I need to calculate all the stores available in banner even if single store select it need to show all the stores)

For Eg: Here in D&D Banner 19 stores are there but [Total Loss Amt] is blank for three stores, so it should return only 16

Thanks

1 ACCEPTED SOLUTION
Community Support

Hi, @Atul8286

I have tried to simulate some of the data hoping to restore your problem.

Measure:

``````The No of stores =
CALCULATE (
COUNT ( [StoreID] ),
FILTER ( ALL ( 'Table' ), [Total Loss Amt] <> BLANK () )
)``````

This is where [Total Loss Amt] is Measure, and the method is the same if [Total Loss Amt] is a column.

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

4 REPLIES 4
Community Support

Hi, @Atul8286

I have tried to simulate some of the data hoping to restore your problem.

Measure:

``````The No of stores =
CALCULATE (
COUNT ( [StoreID] ),
FILTER ( ALL ( 'Table' ), [Total Loss Amt] <> BLANK () )
)``````

This is where [Total Loss Amt] is Measure, and the method is the same if [Total Loss Amt] is a column.

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

Regular Visitor

I have tried this but it return "1" to every row.

Not useful for me.

Thanks

Super User

@Atul8286 I would need sample/example source data to understand what is going on.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Super User

@Atul8286 So like this?

``Calculate(count([StoreID]), All(store[StoreID]), NOT(ISBLANK([Total Loss Amt])))``

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements