Aggregating Only Visible Values in DAX

Alberto Ferrari shows how you can calculate a sum based only on the visible values and ignoring the missing cases:

These measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end of the year, or when the first month with sales is not January as in the following example. As you can see in the following figure, in this case the yearly value of SalesPerWorkingDays is lower than the value of any month, which does not make much sense.

Click through for the solution.

Related Posts

Removing Text Between Delimiters in Power Query

Imke Feldmann has a new M function for us: While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the […]

Read More

Power BI Pareto Charts

Matt Allington shows how you can build up a Pareto chart in Power BI: Pareto Analysis is a statistical technique that applies the Pareto Principle to data. This is more commonly known as the 80:20 Rule. The Pareto Principle is based on the presumption that a relatively small number of inputs (20%)  have most impact […]

Read More

Categories

April 2019
MTWTFSS
« Mar May »
1234567
891011121314
15161718192021
22232425262728
2930