cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ph1234
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
AntrikshSharma
Super User II
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?

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

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

11.png

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
        ADDCOLUMNS (
            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] )
)

 

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors