ALL and ALLEXCEPT = DAX support for a ratio-measure!

[Anonymous]

Hi all,

This is a bit tricky to explain, but I will try my best with help of attached print screens that I put up here:
Please open them and have a look.

https://drive.google.com/drive/folders/1FbNNMIYHnagfuaLxRez3cEXBMZC8NRrR?usp=sharing

I have two fact tables (LOG_Data and FIN_LogUnits Data) that I am trying to combine for a measure. (See attached fact tables.jpeg)

In the LOG_Data, I have statistical information, such as "Tons shipped".
In the FIN_Log_Units Data I have financial information related to costs.

What I want to is to be able to put together a measure called Cost per tons shipped. 

----------------------------------------------------------------------------------------------
First, let's have a look at the numerator (cost) that is coming from FIN_Log_Units Data:

FIN_Log_Units has four fields - 3 dimension fields (TIME, Entity ID, Account) and 1 field containing the data (Periodic).

(See attached jpeg)

In ordet to get total cost, I would do something like:

Cost = SUM('FIN_LogUnits Data'[Periodic]) 

and then be able to filter this depending on time, entity or account (i.e. variable cost account etc.).

----------------------------------------------------------------------------------------------

Second, the denominator (tons shipped) coming from LOG_Data:

LOG_Data has the same four fields - 3 dimension fields (TIME, Entity ID, Account) and 1 field containing the data (Periodic).

(See attached jpeg)

In order to get "Tons shipped", I need to filter the Account field on the value "LOGTONSH", which is the name of that account.
This means that in order to get tons shipped, I can write:

Tons shipped = CALCULATE(SUM(LOG_Data[Periodic]);LOG_Data[ACCOUNT]="LOGTONSH")

----------------------------------------------------------------------------------------------

So my measure could sound quite simple. Just write:

Cost per tons = [Cost] / [Tons shipped]

The problem is that when I do that, I am unable to filter on anything related to the field "ACCOUNT", because it returns a blank.
I assume it has to do with the fact that my denominator is built up by filtering on this field ( i.e. Account = "LOGTONSH").

I want to be able to filter the numerator, [Cost] on all three dimensions (Account, Entity and Time).

But I only want the denominator [Tons shipped] to be filtered on Entity and Time.

----------------------------------------------------------------------------------------------

Example, when I slice my dashboard based on a field in Account_Lookup table [i.e. Variable Cost], I want my numerator to return the values based on 'variable cost' filtered Account field. However, I want [Tons shipped] to stay the same.

In addition, if I would then ALSO slice on an Entity (i.e. Entity no 123) - I want the numerator to return Variable costs for that entity, while the denominator returns Tons shipped for that entity.

But like I said, whenever I slice on anything ACCOUNT related, I get a blank for this measure. 
I have experimented with the ALL and ALLEXCEPT function without any results, probably because I am not trained enough.

I have provided two screenshots from Power BI to demonstrate my issue.

----------------------------------------------------------------------------------------------

Any comments and guidance are highly appreciated!

Regards,
Rasmus

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Anonymous ,

We can try to use this measure as Tons shipped to meet your requirement:

Tons shipped =
CALCULATE (
    SUM ( 'LOG_Data'[Periodic] ),
    FILTER (
        ALLEXCEPT ( 'LOG_Date', 'LOG_Date'[ENTITY_ID], 'LOG_Date'[TIME] ),
        'LOG_Data'[ACCOUNT] = "LOGTONSH"
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.