Dear DNA Team,
I will appreciate your knowledge regarding TOPN formula as today I run into a small issue, which I managed to resolve but I do not understand why I had to apply additional logic.
Top10.pbix (347.0 KB)
As per the attached file top10 formula returns only top 10 customers:
Top 10 = CALCULATE([Sales],
TOPN(10,ALL(Sales[Customer Name Index]),[Sales],DESC),
VALUES(Sales[Customer Name Index]))
Sales = IF(ISBLANK(SUMX(Sales,
Sales[Order Quantity] * Sales[Unit Price] )),BLANK(),SUMX(Sales,
Sales[Order Quantity] * Sales[Unit Price] ))
The same logic I applied to my live model. The live model is the data from a generic mailbox where I count the number of emails received.
EMEA = IF(ISBLANK(COUNTROWS(EMEAPL)),0,COUNTROWS(EMEAPL))
TOP 10 EMEA = CALCULATE([EMEA],
Using the above formula the result I get is:
Everything seems fine apart from that TOP10 didn’t return top 10 email senders.
In order to return only top10 emails senders I added if statement:
TOP 10 EMEA =
VAR TOP10EMA =CALCULATE([EMEA],
My live model pulls the data from 3 separate generic mailboxes then using the below formula I created Unique names of the emails:
Email List = DISTINCT(UNION(DISTINCT(EMEAEXP[Email]),DISTINCT(EMEAPL[Email]),DISTINCT(PLPayables[Email])))
Even though I would create a relationship between support table (unique emails) vs 3 genereic mailboxes and adjust the TOP10 formula to unique email list I would still receive the same result as:
Therefore, do you have an explanation why I had to apply if statement?