cancel
Showing results for
Did you mean:
Frequent Visitor

## Incorrect row values

Hi, I have a matrix that is displaying the row values incorrectly. The matrix shows inaccurate row data but an accurate distinct grand total. If I have a meter that belongs to more than one segment , it's billing date is getting counted in both their total's if the meter had been billed in one segment for a specific date then re-billed in another segment later on in the year for the same date. My data table is below:

Meter     Segment             Billing Start Date
1             Direct                 01/11/2018
1              Fixed                  01/10/2018
1              Flexible             01/10/2018
1             Flexible               01/07/2018
1             Indirect              01/10/2018
1              Indirect            01/08/2018
1             Ultra                01/10/2018

This gives the below matrix output:

Segment    Total Billed Months

Direct                      1

Fixed                       1

Flexible                   2

Indirect                   2

Ultra                       1

Total                      4

Essentially, the column total is correct as there are 4 distinct start dates. However, when this is by row level the start date for one meter can belong to different segments and is counting it more than once. For instance, in the table 01/10/2018 is counted many times but I only want to count it once/ the first occurence of the date - ignoring the other duplicates

5 REPLIES 5
Super User II

@ph1234  for 1/10 how do you establish a relation with Segment, how to know which segment should be considered the first one for this date?

Since 1/10 appears in Fixed, Flexible, and Indirect, which one amongst these 3 needs to considered?

Thank you,
Antriksh Sharma
Frequent Visitor

Hi Antriksh, Thanks for your reply. In this case, ideally I would like to split the count across each segment. So since 1/10 belongs to 3 segments it would count 0.33 across Fixed, Flexible, and Indirect. If that's not possible, ideally which ever is the last segment that the date falls into would be best to count Thanks

Super User II

@ph1234 Not sure if this is the desired result, but I have tried, see if this works. The file is attached below my signature:

``````Total Billed Month =
IF (
ISINSCOPE ( ph[Segment] ),
VAR CurrentSegment =
SELECTEDVALUE ( ph[Segment] )
VAR DatesInCurrentSegment =
CALCULATETABLE (
VALUES ( ph[Billing Start Date] ),
ph[Segment] = CurrentSegment,
REMOVEFILTERS ( ph )
)
VAR SegmentsWithDatesInCurrentSegment =
// Gets the segments that have same dates as in the current segment
CALCULATETABLE (
ph,
ph[Billing Start Date] IN DatesInCurrentSegment,
REMOVEFILTERS ( ph )
)
VAR RankToFindCommonDate =
// Ranking is being done to identify the occurence of dates amongst segments
SegmentsWithDatesInCurrentSegment,
"@Rank", RANKX ( SegmentsWithDatesInCurrentSegment, ph[Billing Start Date] )
)
VAR FilterCommonDate =
// This variable stores the dates that are duplicates amongst segments
FILTER ( RankToFindCommonDate, [@Rank] = 1 )
VAR Result =
// This variable removes the duplicates
EXCEPT ( RankToFindCommonDate, FilterCommonDate )
RETURN
COUNTROWS ( Result ) + DIVIDE ( 1, COUNTROWS ( FilterCommonDate ) ),
DISTINCTCOUNT ( ph[Billing Start Date] )
)
``````

Thank you,
Antriksh Sharma
Frequent Visitor

Thanks Antriksh. Thart is how i'd like it to be split. It seems to have worked in my sample file. However, when I have created the measure in my full dataset (which has sensitive info hence the sample table) I still seem to get the original output.

Segment                Total Billed Months            Forum DAX

Direct                           1                                         1

Fixed                             1                                        1

Flexible                          2                                       2

Indirect                         2                                         2

Ultra                              1                                        1

Total                            4                                        4

Forum Dax is the measure from your post. Total Billed Months = CALCULATE(SUMX(DISITNCT(meter(DISTINCTCOUNT(billing start date)) Thanks

Super User II

@ph1234 Without PBIX file I can't provide much support, there can be a lot of factors that are hindering the calculation.

Thank you,
Antriksh Sharma

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors