Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: How to calculate SUM IF in this particular cas...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Devashish

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
03:21 AM

My data has 2 dates. I have to count how many times they are same BUT not per row but per date1.

For example, I can create a column with simple if statement, or countrows and check "if same". Problem is aggregation. Now on aggregation by date 1 - for 1-jul, 2-jul it will give me 2 and 3. Instead, I am now looking for a calculation measure/column that treats 2 and 3 as 1 count only and then I have to take percentage of total.

What approach should I take?

Basically, I have to count **percentage of times the dates are same**, ignoring the fact if their are more rows per value of date1. (because in actual data I have more columns and there would be more repetition).

Thanks a lot.

Date 1 | Date 2 | If same |

1-Jul | 1-Jul | 1 |

1-Jul | 30-Jun | 0 |

1-Jul | 1-Jul | 1 |

2-Jul | 2-Jul | 1 |

2-Jul | 1-Jul | 0 |

2-Jul | 2-Jul | 1 |

2-Jul | 2-Jul | 1 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

nandic

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
05:12 AM

@Devashish ,

Currently it summarizes values by column "Date 1".

First variable "FilterSameDays" has that part : Summarize(filter([table], value, [column1],[column2],[column3]).

At the moment, column 1 is "Date 1", under column2 and column 3 you can set other columns by which you would like also to group data.

8 REPLIES 8

Highlighted
##

harshnathani

Super User V

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
03:41 AM

Hi @Devashish ,

Not very clear as to what is the expected output.

Share more data and the expected output to help you on this.

Regards,

HN

Highlighted
##

nandic

Solution Sage

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
04:17 AM

Hi @Devashish ,

Try this formula:

Total Same Days =

var filterSameDays = FILTER('Table','Table'[Same Date]>0)

var CountSameDays = COUNTROWS(filterSameDays)

RETURN

IF(CountSameDays>0,1,0)

Cheers,

Nemanja

Nemanja

Highlighted
##

Devashish

Frequent Visitor

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
04:26 AM

Hi Harsh,

This I get on aggregation of above table

Date 1 | If same |

1-Jul | 2 |

2-Jul | 3 |

I want this

Date 1 | New measure | perc |

1-Jul | 1 | 100% |

2-Jul | 1 | 100% |

Total | 2 | 100% |

For ex: I tried using New measure = If([If same]>0,1,0), but then I can not SUM - because the total also says 1. And then I don't know how to calculate % of rows in this new table that it is 1 or 0. It would give me 100% or 0% in each row and then aggreated percentage in total.

**Full story:**

Date 1 - Normal calendar date

Date 2 - Employee logging in the time sheet data

KPI: We want to check on how many times, employee is logging on the same day.

Now, the 8 hours per day are also not logged at once. They can be divided and logged against type of work and even over days. So, maybe on 2nd July someone books 2 hours for travel and 6 hours for work for the work that was done on 1st of July. In this case, the hours are not booked on same day so synchronization against Date 1 for 1st July should be 0%.

So I tried with countrows+filter and if statement - in the last 2 rows. The problem is it is giving me total of number of rows because the hours booked are broken up against different areas (travel, work etc). So, basically, these 5,5, 4, 10 all means 1 to me and 0 means 0. How to achieve this? Thanks

Highlighted
##

Devashish

Frequent Visitor

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
04:28 AM

Hi,

Thanks for your support.

The problem is Total = 1. I want to count how many times it is 1. That is the whole point of having these 1s.

Highlighted
##

nandic

Solution Sage

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
04:42 AM

Hi @Devashish ,

Try this option:

Total Same Days 2 =

var filterSameDays =

ADDCOLUMNS(

SUMMARIZE(FILTER('Table','Table'[Same Date]>0),'Table'[Date 1]),

"If Same Day",IF(COUNTROWS('Table')>0,1,0)

)

var CountSameDays = COUNTROWS(filterSameDays)

var Counter = IF(CountSameDays>0,1,0)

RETURN

IF(HASONEVALUE('Table'[Date 1]),Counter,SUMX(filterSameDays,[If Same Day]))

Cheers,

Nemanja

Cheers,

Nemanja

Highlighted
##

Devashish

Frequent Visitor

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
05:02 AM

Hi Nemanja,

Thanks, it seems to be working now.

Please check my Message 4 above. I want this information to be broken up by WORKCENTER (per employee)

What changes do I have to make in the code?

Highlighted

nandic

Solution Sage

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
05:12 AM

@Devashish ,

Currently it summarizes values by column "Date 1".

First variable "FilterSameDays" has that part : Summarize(filter([table], value, [column1],[column2],[column3]).

At the moment, column 1 is "Date 1", under column2 and column 3 you can set other columns by which you would like also to group data.

Highlighted
##

Devashish

Frequent Visitor

Re: How to calculate SUM IF in this particular case?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2020
05:17 AM

Thanks for explaining. I will explore more on SUMMARIZE DAX. Seems very useful.

Announcements

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors

User | Count |
---|---|

49 | |

45 | |

19 | |

16 | |

12 |