cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Correct Subtotal with a twist

Hi,

I am trying to show project status by quarter and have to fill in quarters where there is no change in activity.

I want the flow distribution column to be 3 (the total for each quarter)

but using the has one value function in measure 3, I get 4.

Flow distribution is defined as being if the project is active or not in that quarter and I had to fill this in for Q2.

Can someone please help as this is usually so easy so I am wondering why it doesn't work.  Thanks

1 ACCEPTED SOLUTION
Super User

@bcobrien1977 So, is this the total for the Flow Distribution? And the Flow Distribution is your Feature Epic Story Point Count measure? If this is the case, then it should be something like this:

``````Flow Distribution Total =
VAR __Table = SUMMARIZE( 'vwFiscalCalendar', [Fiscal Year Quarter], "__Value", [Feature Epic Story Point Count] )
VAR __Result = IF( HASONEVALUE( 'vwFiscalCalendar'[Fiscal Year Quarter]), [Feature Epic Story Point Count], SUMX( __Table, [__Value] ) )
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@bcobrien1977 What is your measure formula?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper IV
Total = if(HASONEVALUE('vwFiscalCalendar'[Fiscal Year Quarter]),[Feature Epic Story Point Count],sumx('Flow Distribution','Flow Distribution'Feature Epic Story Point Count]))

Here is the data. I am trying to get a count of 3 (1 for each row).  It's a bit involved as to how this is dervied because of the data in the gaps and every IssueKey needs a record for every quarter if not done.

Status Text = min('Flow Distribution'[Final Status])

STATUTS TEXT DATE = CALCULATE([Status text],CALCULATETABLE(LASTNONBLANK('vwFiscalCalendar'[CalendarDate],CALCULATE([Status text])),DATESBETWEEN('vwFiscalCalendar'[CalendarDate],MINX(ALL('vwFiscalCalendar'),'vwFiscalCalendar'[CalendarDate]),Max('vwFiscalCalendar'[CalendarDate]))))

MAX DONE DATE = calculate(max('Flow Distribution'[CreatedOnNext]),'Flow Distribution'[Final Status]="Done")

Distribution = if(AND([STATUTS TEXT DATE]="Done",[Done status achieved on]=BLANK()),BLANK(),[STATUTS TEXT DATE])

THanks

Super User

@bcobrien1977 So, is this the total for the Flow Distribution? And the Flow Distribution is your Feature Epic Story Point Count measure? If this is the case, then it should be something like this:

``````Flow Distribution Total =
VAR __Table = SUMMARIZE( 'vwFiscalCalendar', [Fiscal Year Quarter], "__Value", [Feature Epic Story Point Count] )
VAR __Result = IF( HASONEVALUE( 'vwFiscalCalendar'[Fiscal Year Quarter]), [Feature Epic Story Point Count], SUMX( __Table, [__Value] ) )
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper IV

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors