cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Measure to count total rows that doesn't depend on row grouping

Suppose I have a CSV table such as this.

MainUnit	SubUnit
A	A1
A	A2
A	A3
B	B1
B	B2
B	B3
B	B4
C	C1
C	C2
D	D1
D	D2
D	D3

 

My goal is to calculate, for each MainUnit, the % of it's sub unit count considering all sub units. I am aware that I can create a measure such as below,

UnitCount = COUNTA(UnitSubUnit[SubUnit])

and then in the table set it as [ Show value as --> Percent of grand total ] and it will get me the desired percentage.

 

But that's not quite what I'm looking for. What I want is a 'TotalUnitCount' measure, which will retun 12 in this example for each row in my table. The reason being I want to use the UnitCount and TotalUnitCount elsewhere in my project, including as parts of other measures. Essentially, this is what I'm looking for.

 

Untitled.png

 

But it appears that if I calculate a measure like this, it will still not actually return the total number of rows, but rather still grouped acording to the row selection.

 

TotalUnitCount = COUNTROWS(UnitSubUnit)

 

When I attempted that, what I get is below; both UnitCount and TotalUnitCount return the same value.

 

Capture.PNG

 

I don't want to set a measure like 'TotalUnitCount = 12' hardcoded for two reasons.

  1. The MainUnit and SubUnit count can change over time.
  2. I want to be able to have a Page Level filter to filter MainUnit, and it should reflect the calculation accordingly. For example, if I chose my page level filter to select only A and C, then my desired output should be like this:

    Untitled.png 

Is this a possibility without hardcoding a measure?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@Sach 

Here are the three measures and it will work when set the page filter as well.

Unit Count = COUNTROWS(table6)
-------------------------------------------
Total unit Count = 
CALCULATE(
    [Unit Count],
    ALLSELECTED(Table6[MainUnit])
)
-------------------------------------------
Percentage = 
DIVIDE(
    [Unit Count],
    [Total unit Count]
)

Fowmy_0-1599851347386.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn




View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

@Sach 

Here are the three measures and it will work when set the page filter as well.

Unit Count = COUNTROWS(table6)
-------------------------------------------
Total unit Count = 
CALCULATE(
    [Unit Count],
    ALLSELECTED(Table6[MainUnit])
)
-------------------------------------------
Percentage = 
DIVIDE(
    [Unit Count],
    [Total unit Count]
)

Fowmy_0-1599851347386.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn




View solution in original post

Highlighted

This worked, thanks!

 

So to make sure I understand, the CALCULATE() function sums up UnitCount, across all MainUnit rows available?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors