LOD Calculations

[asdf1608]

I am trying to do a LoD Calculation in Power BI - but it doesn't work like it does in Tableau and I cant figure out what I am doing wrong.

Calculations I am using in Tableau Listed Below

Document LOD- "This Calculations sums all sales on a specfic Document Number"

{FIXED [Document Number]: SUM( [Sales]+[Sales1]) }

Amount Sales LOD - "This Calculation shows the overall amount for that document number"

{FIXED [Document Number]:
SUM([Amount])/SUM([Document LOD]) }

Power BI Formulas I have.

Document (LOD): - WORKS!

CALCULATE([All Sales],ALLSELECTED(Sheet1[Document Number]))

Amount - Sales LOD - Does not give me the correct answer :

Amount - Sales LOD = CALCULATE(

    SUM(Sheet1[Amount])/SUM(Sheet1[Document (LOD)]),

    ALLSELECTED(Sheet1[Document Number])

    )

can anyone state me the reason why the amount-sales LOD is wrong in power BI

I'm attaching the sample file here
dsample.pbix

Help with this is really appreciated
Thanks in advance

[asdf1608]

@OwenAuger 

Thank you so much for your explanation. 

It works.

[OwenAuger · Accepted solution]

Hi @asdf1608 

This ended up a bit long-winded as I wrote it I'm afraid.😅

I have attached the edited PBIX in any case so you can look at the code there.

To replicate the behaviour of a FIXED LOD expression from Tableau in DAX, one safe method I would recommend is:

1. Take the fields specified before the colon in the FIXED expression and store their values in a variable. In your example this is just Document Number. You can use VALUES for a single field or SUMMARIZE for multiple fields, wrapped in CALCULATETABLE if there is a row context. Let's call this variable in the current example CurrentDocumentNumber.
2. Then take the expression after the colon in the FIXED expression, and in DAX, evaluate that within CALCULATE, with modifiers REMOVEFILTERS (), and CurrentDocumentNumber.

In other words, the general conversion of a FIXED LOD expression to DAX is:

// Tableau
{ FIXED <Field_1>, <Field_2>, ... ,<Field_n> : <Expression> }

becomes

// DAX
VAR CurrentFieldValues =
    // Note: CALCULATETABLE only required in the case of row context
    // SUMMARIZE can be replaced with VALUES if there is only one <Field>
    CALCULATETABLE (
        SUMMARIZE ( YourTable, <Field_1>, <Field_2>, ... ,<Field_n> )
    )
RETURN
    CALCULATE (
        <Expression>,
        REMOVEFILTERS ( ),
        CurrentFieldValues
    )

Also, a SUM ( { FIXED ... } ) expression requires the DAX expression to be wrapped in SUMX (see below).

Below is the actual code for your example:

For the calculated column Document (LOD):

// Tableau
Document LOD =
{ FIXED [Document Number]: SUM ( [Sales] + [Sales1] ) }

becomes

// DAX
Document (LOD) = 
VAR CurrentDocumentNumber =
    CALCULATETABLE ( VALUES ( Sheet1[Document Number] ) )
RETURN
    CALCULATE(
        [All Sales],
        REMOVEFILTERS (),
        CurrentDocumentNumber
    )

Explanation:

In Tableau, the FIXED expression (in this case used to define a column) calculates the sum of [Sales]+[Sales2] aggregated over all rows where Document Number matches the current row, with all other field values on the current row ignored.

In DAX, when CALCULATE is used in a calculated column, the current row of the table is first applied as a filter, then REMOVEFILTERS clears all resulting filters, and CurrentDocumentNumber restores the Document Number.

For the Measure: Amount - Sales LOD

// Tableau, effective expression when aggregated using SUM
Amount Sales LOD =
SUM ( 
  { FIXED [Document Number]: SUM ( [Amount] ) / SUM ( [Document LOD] ) }
)

becomes

// DAX
Amount - Sales LOD = 
SUMX (
    VALUES ( Sheet1[Document Number] ),
    VAR CurrentDocumentNumber =
        CALCULATETABLE ( VALUES ( Sheet1[Document Number] ) )
    RETURN
        CALCULATE (
            SUM ( Sheet1[Amount] ) / SUM ( Sheet1[Document (LOD)] ),
            REMOVEFILTERS (),
            CurrentDocumentNumber
        )
)

Explanation:

In Tableau, the SUM(...)/SUM(...) expression is evaluated for each Document Number, with all other filters ignored. The resulting values for each Document Number are then summed.

In DAX, SUMX iterates over the distinct values of Document Number. For each Document Number, it uses CALCULATE to apply the Document Number as a filter, then REMOVEFILTERS clears all filters that might be present, and CurrentDocumentNumber re-applies the Document Number filter. The resulting values for each Document Number are then summed.

Alternative using ALLEXCEPT

A alternative you could use in your particular case is to use ALLEXCEPT within CALCULATE. However, I wouldn't recommend this in general, as it assumes that there is an explicit filter on the column that appears before the colon in FIXED.

For reference, the ALLEXCEPT versions would be:

Document (LOD) = 
CALCULATE(
    [All Sales],
    ALLEXCEPT ( Sheet1, Sheet1[Document Number] )
)

Amount - Sales LOD = 
SUMX (
    VALUES ( Sheet1[Document Number] ),
    CALCULATE (
        SUM ( Sheet1[Amount] ) / SUM ( Sheet1[Document (LOD)] ),
        ALLEXCEPT ( Sheet1, Sheet1[Document Number] )
    )
)

Regards