cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Sum of Distinct Values over Total Distinct by Category

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.

Regards,

Summer

1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Super User

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

Helper III

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

Community Support

Hi @summer18 ,

Best Regards,

Icey

Helper III

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

Community Support

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.

Helper III

Thanks @Icey .  It works!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.