Level of Detail Issues

[Anonymous]

Dear Team,

I have to calculate distinct count fixing on three columns, and then use that distinct count converted into a string.

So I use, first, this formula -- Calculate(DistinctCOunt(URN), Country, VendorID, Invoice number) and then create a column point to this calculation and convert to the text, all works fine and looks good, BUT values are a bit off.

when I filter on country gives me correct value but when selecting all, the values are off, doesn't match to the raw data. below is the link to excel, I had created pivots and output tabs. also my sample only one country and vendor could be the same in two different countries, so i use Country and vendor id both in the level of detail to get the correct count. Please suggest.

https://drive.google.com/file/d/1mv6PLQuO9DlWrFL0BA3Cks_o7rsqqGhg/view?usp=sharing

Cheers,

Shilpi

[v-yulgu-msft]

Hi @Anonymous ,

If you want to calculate the distinct count for [URN] per country per VendorID and per Invoice number, you just need to add these fields into a Table visual, and choose "Count(distinct)" as aggregation type for [URN].

However, I cannot understand what "create a column point to this calculation and convert to the text." means. Is the screenshot in "Output" tab your expected result? Can you provide more detailed description about the desired result? 

Regards,

Yuliana Gu