Hi community!

Let’s suppose this is my dataset:

Brand | Country | Sales |

A | IT | 6 |

A | FR | 5 |

A | DE | 3 |

B | IT | 7 |

B | FR | 4 |

B | ES | 1 |

I would need a calculated field that shows me this value (in red):

Brand | Sales Value | Sales tot |

A | 14 | 26 |

B | 12 | 26 |

This would help me to compute the percentage of sale made by A over total: 14/26

Let’s suppose then that I want to filter by Country.

If I filter by “IT” I expect this result:

Brand | Sales Value | Sales tot |

A | 6 | 13 |

B | 7 | 13 |

This would help me to compute the percentage of sales made by A in Italy over total in Italy: 6/13

How can I obtain such calculated field?

I tried with {FIXED Country : Sum(Sales)} that works well when I filter by “IT”, but that does not work when I do not use the filter. This is the result I obtain:

Brand | Sales Value | Fixed |

A | 14 | 25 |

B | 12 | 23 |

It happens because there is no row containing sales in ES by brand A neither sales in DE by brand B.

A “workaround” is to transform underlying data in this way:

Brand | Country | Sales |

A | IT | 6 |

A | FR | 5 |

A | DE | 3 |

A | SP | 0 |

B | IT | 7 |

B | FR | 4 |

B | SP | 1 |

B | DE | 0 |

Obviously, I am simplifying. My dataset is much larger and I have to compute such percentages over different dimensions. The “workaround” is working, but the number of rows are growing up day by day. I cannot go on like this and I really need something different to solve this issue.