Sum of Distinct Values over Total Distinct by Category

[summer18]

Hi,

I need assistance on the formula for computing % of new products over total.  I could not achieve the desired output.

Formula should be New Product Count / Total by Region. 

Eg.  for Asia and Enterprise = 7 / 113 (total for Asia) = 6.19%

Correct %:

The formulas I used:

1) Count of NEW Products = 

CALCULATE(

                  DISTINCTCOUNT(DATA[ProductID]),

                  DATA[Status] IN {"New"})

2) Total Count of Products =

SUMX(

          KEEPFILTERS(VALUES(DATA[Region])),

          CALCULATE(DISTINCTCOUNT(DATA[ProductID]))

)

3)  % New Products = IFERROR([Count of NEW Products]/[Total Count of Products],0)

Attaching the links for the pbix and sample data with computation.

https://drive.google.com/drive/folders/1d1ivWVWhnNzfcUGdseXiTY71pldhct5M?usp=sharing

Thanks in advance for your help.

Regards,

Summer

[amitchandak]

@summer18 , I think there some different data in pbix. Can you please check.

[summer18]

Hi @amitchandak , Thanks for checking into this.  I updated the link

https://drive.google.com/drive/folders/1d1ivWVWhnNzfcUGdseXiTY71pldhct5M?usp=sharing

[Icey]

Hi @summer18 ,

I have no access to your shared file. Please share it publicly.

Best Regards,

Icey

[summer18]

Hi @Icey , I just made the link public.  Hope you can help me on this

[Icey · Accepted solution]

Hi @summer18 ,

Try this:

% New Products =
IFERROR (
    [Count of NEW Products]
        / CALCULATE ( [Total Count of Products], ALLSELECTED ( DATA[Segment] ) ),
    0
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[summer18]

Thanks @Icey .  It works!