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
JPY
Helper II
Helper II

How to sum the measure

Hi all,

1. I added a calculated column by this formula: 

Column = CALCULATE(DISTINCTCOUNT(Query1[CreationTimeUTC.1]),ALLEXCEPT(Query1,Query1[User Name],Query1[CreationTimeUTC.1].[Month]))
2. I added a measure based on the column:
Distinct Sum = SUMX(DISTINCT(Query1[Column]),Query1[Column])help.PNG

 

I want to create a measure to sum up all the numbers by months.
For example, I want it all showing 9 next to the distinct sum column depending on how many months I choose. Because there are 3 datapoints in July 2018, 2 in August 2018, 2 in September 2018, 1 in October 2018 and 1 in February 2019 for the particular user name. Adding all these datapoints would tally up to 9.

 

Kindy pls give any suggestion

 

Thanks!

JPY

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @JPY 

You could use this formula:

Measure = var _table= SUMMARIZE(Query1,Query1[User Name],Query1[CreationTimeUTC.1],"Column",DISTINCTCOUNT(Query1[Column])) return
SUMX(_table,[Column])

Result:

7.JPG

 

Best Regards,

Lin

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

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

hi, @JPY 

You could use this formula:

Measure = var _table= SUMMARIZE(Query1,Query1[User Name],Query1[CreationTimeUTC.1],"Column",DISTINCTCOUNT(Query1[Column])) return
SUMX(_table,[Column])

Result:

7.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@JPY -

Whenever you want to get a total value in your measure, it means that you need to remove a filter. To remove a filter, you need to apply some sort of ALL. Which ALL function to use and which columns are relevant is situational - which column(s) need a modified filter context?

Cheers,

Nathan

Hi Nathan @Anonymous ,

Thanks for your reply!

In my dataset, I have mutiple different users and creation times. 

Besides, I have Year and Month slicer in my dashboard. I want the measure can sum up the "distinct sum" according to month and year. For example, if I choose July 2018, Auguest 2018, September 2018 and Feb 2019 then the measure will tally to 9 as per my screenshot above.  Since I want to group the measure so that I can have a slicer to filter.

 

Group = IF(AND([NEW MEASURE]>0, [NEW MEASURE]<=5),"0-5",
      IF(AND([NEW MEASURE]>5, [NEW MEASURE]<=10),"5-10",
       IF([NEW MEASURE]>10,"10+", BLANK())
))

 

 

 

help1.PNG

 

Or do you know there is another way to achieve it? 

Thanks.

JPY

Anonymous
Not applicable

@JPY  - 

To get the Total, you will want to use ALLSELECTED.

I don't think you'll be able to use a measure as a slicer though.

 

Hi @Anonymous,

Could you please explain in more detail? Where should I apply the Allselected formula?

Thanks.

 

JPY

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.