Set analysis help

[mahitham]

Hi Experts,

Can any one please help me on below issue.

I have used below expression in KPI

=COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id) getting 46 as Count

In a Report I have taken

MonthName                                                                                                                            as Dimension and

=COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id)    as Measure

then getting

MonthName     Measure

Mar 2018         44

---------------

When I have added another Dimension i.e., Product then we have 46 as Total

MonthName      Product       Measure

March 2018      Product1       32

March 2018      Product2       4

March 2018      Product3       3

March 2018      Product4       2

March 2018      Product5       2

March 2018      Product6       1

March 2018      Product7       1

March 2018      Product8       1

Total                                      46

In the above KPI I have to get 46 like above report instead of 44. Please help me to get 46 on kpi.

thanks in advance.

[YoussefBelloum]

Hi,

try this:

=Aggr(COUNT({<[MonthName]={'$(=MonthName(Max([Date])))'},Product={'*'}-{' '}>}distinct id),MonthName)

[jonathandienst · Accepted solution]

Expressions with distinct are dependent ion the context and are not necessarily additive. The KPI will apply the distinct to the entire model. The table values will find distinct values in the month, but a value could appear in more than one month.

You can calculate a sum of rows value

=Sum(Aggr(Count({<MonthName = {"$(=MonthName(Max([Date])))"}, Product -= {' '}>} Distinct id), MonthName))

[mahitham]

Hi Jonathan,

Thanks for your reply.

After adding Productname to your expression working fine.

=Sum(Aggr(Count({<MonthName = {"$(=MonthName(Max([Date])))"}, Product -= {' '}>} Distinct id), MonthName,Productname))