Replicating a power bi calculation using ALLSELECTED

[vkedia]

Greetings, I am trying to recreate a calculation in QuickSight that I once had in Power BI. I have my total customers, which are segregated into various levels as shown below. When I take a simple sum of my members, it automatically splits the number by the levels. However, in the third column, the total number of members is constant. I used the given DAX code to achieve this:

Total Members =
VAR Memb = CALCULATE(SUM(fact_customer_last_activity[no_of_members]),ALLSELECTED(fact_customer_last_activity))

RETURN IF(ISBLANK(Memb),0,Memb)

The ALLSELECTED function allowed me to block it from getting divided by the level names. Is there any way I could get this in QS?

Thank You!

[Xclipse]

Hi @vkedia

Please try sumOver ensures that the sum is computed across all rows (ignoring the level or any filters applied).

Example: (Syntax may differ - replace the fields from your dataset).

sumOver(
    {no_of_members}, 
    [],
    PRE_AGG
)

[Giridhar.Prabhu]

Hi @vkedia

You can use the sumOver function. An example is provided below

Use the article below for further reading

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[vkedia]

Thank you so much! It is working