Trying to add dax to calculate all filters besied one.

[jaryszek]

Hi Guys,

I have used this dax on my fct_table:

% of Instance Total Cost Test = 
        DIVIDE(
            Sum(Fct_EA_AmortizedCosts[CostInBillingCurrency]),
        CALCULATE(
            SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency]),
            Fct_EA_AmortizedCosts[PricingModel] = "OnDemand"
        ) 
        )

Page Filter is also "OnDemand". 

But it is always returning 100%...

Why? 

Generally i want to divide row filter context in table by sum of all this rows: 

but it is somehow giving 1,00 (100 %)...

Can anybody help ?

Goal: 
Keep filter context in table and always take current row sum of costs and divide by total row created from filter context...
why this is not working what i did? 

Best,
Jacek 

[v-karpurapud]

Hi @jaryszek 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Jihwan_Kim  and @MasonMA for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[jaryszek]

Thank you very much

Ok It worked but it is across all dates, so it means that it is summing up to 100 % only If full date range is chosen.
I want to get the 100 % in current filter context in table per exact day, Subscription slicers filters... (and i will be adding more in the future so i do not want to just list all slicers fields, i want to have generic solution). 

How to make this? 

I am attaching example model (Filter context tab):
LINK 

Best,
Jacek

[MasonMA]

Hi,

It helps when see your sample file. Let me know if this would be the result you were looking

If it is you can use ALLSELECTED() to restore last shadow filter contexts on any column as your Selected SUM in below for your Denominator in '% of Instance Total Cost Test'.

SelectedSUM = 
 CALCULATE (
        SUM ( fct_amortizedcosts[CostInBillingCurrency] ),
        ALLSELECTED())

[Jihwan_Kim]

Hi, 

Without seeing the semantic model, I am not sure what sort order column is implemented or what other columns are influencing the filter context on the page, but please try something like below whether it works.

% of Instance Total Cost Test =
DIVIDE (
    SUM ( Fct_EA_AmortizedCosts[CostInBillingCurrency] ),
    CALCULATE (
        SUM ( Fct_EA_AmortizedCosts[CostInBillingCurrency] ),
        REMOVEFILTERS ( Fct_EA_AmortizedCosts ),
        VALUES ( Fct_EA_AmortizedCosts[PricingModel] )
    )
)

[MasonMA]

Hi Jacek,

For the denominator, you are calculating total cost of all rows visible under the page filters, but ignoring the row-level split. You'd need to clear the row-level filter while keeping the page filters with ALL() or REMOVEFILTERS() at the right level: 

CALCULATE(
SUM ( Fct_EA_AmortizedCosts[CostInBillingCurrency] ),
REMOVEFILTERS(Fct_EA_AmortizedCosts[PricingModel])

)