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
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
@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:
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.