Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum each row of table based on distinct count of ID in another column?

So my data is as follows:

Example.jpg

 

What the measure would need to do by each row is take the amount, and then divide that amount by a distinct count of the year column based on that row's Month + Day value. Then I would need to sum each row based on that calculation.

 

So for example I am only going to look at the month + day of 0422. If I select 2016 and 2017 as a filter, it should do a distinct count of the year column per Month + Day value. So for 0422, the distinct count would be 2 as 2016 and 2017 is present. The amount column should then be divided by this result for each Month + Day. So ID 1 would result in 5 (10/2), ID 2 10 (20/2), and ID 5 50 (100/2). The measure would sum these values.

 

As an aside, the month + day of 0323 (ID 6) would result in 10. As when the table is filtered for 2016 and 2017, 0323 only has a distinct count of 1 for the year column. So the amount of 10 divided by 1.

 

If I select 2016, 2017, and 2018 as a filter then 3 would be the distinct count for 0422. So ID 1 would then result in 3.3 (10/3), ID 2 6.6 (20/3), ID 3 10 (30/3), and ID 5 33.3 (100/3). The measure would then sum these values.

1 ACCEPTED SOLUTION

@Anonymous add another measure

 

Correct Measure = 
SUMX ( VALUES ( Table[Business Day] ), [1D] )

 

or you can club 1D into this measure

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@Anonymous try this

 

My Measure = 
VAR __distinctyear = DISTINCTCOUNT( Table[Year] )
RETURN
DIVIDE ( SUM ( Table[Amount] ), __distinctYear )


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2kYour measure is exactly right, but half way since I'm using the measure in a matrix. It's right at the row level, but the total does not equal the sum of each row.

 

2020-04-27_10-02-59.jpg

 

1C = DISTINCTCOUNT('Table'[Year])
1D = DIVIDE(SUM('Table'[Amount]),[1C],0)

 

So that result is the same as your My Measure suggestion. This matrix is being filtered by the year 2016 and 2017 and the month of April. Business Day is the root of my Month + Day Column. So the first row would have a Month + Day ID of 0419, the second row 0420, and so on...

 

So it's correct at each row, but the total is not the sum of the rows above it. I understand why that is because it's dividing the $15M amount by 2, the result of 1C.

 

I'm trying to figure out a way to correctly calculate the total which in this case should be 9,293,973 (the sum of each row).

 

I understand your measure as well, and I would need the total to iteratively sum that calculation instead of the total performing the calculation at the "total" level.

 

@Anonymous add another measure

 

Correct Measure = 
SUMX ( VALUES ( Table[Business Day] ), [1D] )

 

or you can club 1D into this measure

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k Perfect! Thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.