Forum Discussion
Helper ICalculation help required for Distinct Count summing upto Totals with different buckets
Hi There,
I have a specific requirement where I need to find the distinctcount that is summing up to the total records. Sample pbi is attached here. https://drive.google.com/file/d/1Jb5izDKPf3bcLCIXylqSH2-e1xczSDiQ/view?usp=sharing
My data has 106 records but only 97 distinct request id's. All the records results from either 'Frequency change', size change or quantity change and the combination. (Combination: Any two or three changes happen for a unique Reqest is considered as Combination change.)
I have 76 frequency changes, 4 qty changes, 17 size changes and 1 combination change ---> 76+4+17+1 = 98, however my distinct count in the table is 97 only. This is happening because: the customer REQXXX1110 has 1 frequency change & 1 size change as a combination change and another as size only changes. This makes the individual changes are showing as 98, instead of 97. Look at the picture below. This guy has Combination Only & Size Only in this week.
All I need is:
If the same user has a combination and also any of other 3 changes, I need to consider all of them as a single combination. transaction. For the given data in PBI file, I want the below output. Would it be possible using DAX or do i need handle this in ETL?
As REQCXXXX1110 has a more than one change, I would like to consider him as one user having one combination change. Becuase doing more than one is always a combination. I need to get 16 instead of 17 in QTY ONLY count.
Hi, pprasad2
I think that the measure you wrote is right. The actual reason why the total value of this measure[Size Change COUNT] become 18 is the existence of this row of data, as shown in the figure:
You can see that the value of [REQC_CHANGE_ID] is blank, and the value of [Size Only] is 23, I think there may be some problems.
I want to check if you want to keep this row of data. If you want to keep this row of data, you can use the filter on the right side of the report page, like this:
And you can get what you want, like this:
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
pprasad2 , Not very clear. But if you want to sum distinct count after a level. You can use values or summarize
Measure = sumx(values(Table[Date]), calculate(distinctcount(Table[col])))
Check how context is used
- v-robertq-msft
Community Support
Hi, pprasad2
I think that the measure you wrote is right. The actual reason why the total value of this measure[Size Change COUNT] become 18 is the existence of this row of data, as shown in the figure:
You can see that the value of [REQC_CHANGE_ID] is blank, and the value of [Size Only] is 23, I think there may be some problems.
I want to check if you want to keep this row of data. If you want to keep this row of data, you can use the filter on the right side of the report page, like this:
And you can get what you want, like this:
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
