KPI calculation question

[Anonymous]

Hi all,

Sorry to bother you but I am struggling with this issue and I can't do what I want.

I have a fact table that looks like this :

Action ID	Ticket ID	Sales	Discount sales
A	1	-	5.99
A	2	-	6.99
-	1	11.98
-	2	17.98
-	3	35.99

I want to create a chart table with the following result :

Action	Sum of sales for total tickets linked to an action id
A	29.96 (=11.98+17.98)

Can someone help me write the formula of the expression to get this result please ?

Thanks in advance,

Regards,

Sophie

[Anil_Babu_Samineni]

May be this?

Sum({<[Ticket ID] = P({<[Action ID] = {A}, [Ticket ID] = P({<[Action ID] = {A}>} [Ticket ID])>}[Ticket ID])>} Sales)

[RonaldDoes]

Hi Sophie,

How about:

=Sum(Sales)

Please find attached an example.

Hope this helps you.

With kind regards,

Ronald

[Anonymous]

Thanks for your answers Ronald, but I think I have explained my initial table incorrectly because I forgot about the filed Item ID.

Here is the complete fact table

Action ID	Ticket ID	Item ID	Sales	Discount sales
A	1	RC1	-	5.99
A	2	RC2	-	6.99
-	1	RC3	1.98
-	1	RC4	10.00
-	2	RC5	7.98
-	2	RC6	10.00
-	3	RC7	35.99

I tried your solutions and it doesn't work...

Any other ideas ?

Regards,

sophie

[Anonymous]

Thanks but since I am in a simple table chart, how can I assign [Action ID] = {A} ? What if I have an action id = B ?

[aarkay29]

Can you please tell us Why Ronald's solution is not working for you?

I mean where it is giving you wrong!

[Anil_Babu_Samineni]

My bad, stalwar1‌ may have some inputs?

[Anonymous]

It doesn't work because in my qvd, I have multiples lines per ticket because of item id.

[sunny_talwar · Accepted solution]

May be this

=Sum(Aggr(Sum(TOTAL <[Ticket ID]> Sales), [Action ID], [Ticket ID]))

[RonaldDoes]

Hi Sophie,

Ok, that really doesn't change much with regards to the solution, but I've changed the demo accordingly.

SourceTable:
LOAD * INLINE [
    ActionID, TicketID, ItemID, Sales, Discount sales
    A, 1, RC1, , 5.99
    A, 2, RC2, , 6.99
    , 1, RC3, 1.98,
    , 1, RC4, 10.00,
    , 2, RC5, 7.98,
    , 2, RC6, 10.00,
    , 3, RC7, 35.99,
];

Actions:
LOAD
    ActionID,
    TicketID,
    [Discount sales]
Resident SourceTable;

Tickets:
LOAD
    TicketID,
    Sales
Resident SourceTable;

DROP Table SourceTable;

Hope this helps you.