Error on total with measures

[SebSchoon1]

Hi guys,

I'm trying to get the TOTAL Items Between any warehouse

Divided By the total number of warehouse which have a Rank 

I'm not able to get the right measure :'(

here is my table

My formula is

Répartition = VAR QTETOTDEP = [Total quantités tous dépots]

              VAR TOTDEPOT  = [Total dépots]

              VAR RESULT = DIVIDE(QTETOTDEP,TOTDEPOT,BLANK())

              Return RESULT

My Model is 

File example 

https://we.tl/t-KwOgHOZDJF

[johnt75 · Accepted solution]

You can use

Répartition =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Tableau Dispatch',
            'Base Article'[CODE ARTICLE],
            'Tableau Dispatch'[CODE DEPOT]
        ),
        "@val",
            VAR QTETOT = [1-Total articles en stock]
            VAR QTETOTDEPOT =
                CALCULATE (
                    [1-Total articles en stock],
                    REMOVEFILTERS ( 'Tableau Dispatch'[CODE DEPOT] )
                )
            RETURN
                DIVIDE (
                    CALCULATE ( QTETOTDEPOT, ALL ( 'Tableau Dispatch'[CODE DEPOT] ) ),
                    [Total dépots],
                    BLANK ()
                )
    )
RETURN
    SUMX ( SummaryTable, [@val] )

[johnt75 · Accepted solution]

You can use

Répartition =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Tableau Dispatch',
            'Base Article'[CODE ARTICLE],
            'Tableau Dispatch'[CODE DEPOT]
        ),
        "@val",
            VAR QTETOT = [1-Total articles en stock]
            VAR QTETOTDEPOT =
                CALCULATE (
                    [1-Total articles en stock],
                    REMOVEFILTERS ( 'Tableau Dispatch'[CODE DEPOT] )
                )
            RETURN
                DIVIDE (
                    CALCULATE ( QTETOTDEPOT, ALL ( 'Tableau Dispatch'[CODE DEPOT] ) ),
                    [Total dépots],
                    BLANK ()
                )
    )
RETURN
    SUMX ( SummaryTable, [@val] )

[SebSchoon1]

You are God like,

i'll inspect your formula! to understand it!

[SebSchoon1]

@johnt75 

Hello, Tank you!

Actually it send me an error ^^ On the Return part

🙂 

May I ask you what are the mechanics behind?

[johnt75]

Its effectively recreating the table visual. By doing a SUMX over the summary table it is doing the calculation line by line and then creating the total, rather than just doing the calculation for all rows at the same time.

[SebSchoon1]

@johnt75 

Hi Johnt, I try to sum the Truncated values (integer part)

with 

TRUNC([Répartition])

But it gives me 13 as total Value,

even with a summarized table.

Any workaround?

[johnt75 · Accepted solution]

you'd need to move the TRUNC into the measure so the last line becomes

SUMX ( SummaryTable, TRUNC( [@val] ) )

[SebSchoon1]

@johnt75 

Last question then i'll have eveything i'll need to understand more of Dax context

I i'd like to compute The Total decimal part but show in rows the Decimal Values times the total number of warehouses?

To Get 4 as Total 

but 2 in each Rows

because 0.67 *3 = 2 

🙂

[johnt75]

You'd need 3 more measures

Répartition Decimal Line Item = 
VAR QTETOT = [1-Total articles en stock]
VAR QTETOTDEPOT =
    CALCULATE (
        [1-Total articles en stock],
        REMOVEFILTERS ( 'Tableau Dispatch'[CODE DEPOT] )
    )
VAR Total =
    DIVIDE (
        CALCULATE ( QTETOTDEPOT, ALL ( 'Tableau Dispatch'[CODE DEPOT] ) ),
        [Total dépots],
        BLANK ()
    )
VAR TotalAsInt = TRUNC( Total )
VAR NumDepots = COUNTROWS(ALLSELECTED( 'Tableau Dispatch'[CODE DEPOT]))
RETURN (Total - TotalAsInt ) * NumDepots 

Répartition Decimal Total = 
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Tableau Dispatch',
            'Base Article'[CODE ARTICLE],
            'Tableau Dispatch'[CODE DEPOT]
        ),
        "@val",
            VAR QTETOT = [1-Total articles en stock]
            VAR QTETOTDEPOT =
                CALCULATE (
                    [1-Total articles en stock],
                    REMOVEFILTERS ( 'Tableau Dispatch'[CODE DEPOT] )
                )
            VAR Total =
                DIVIDE (
                    CALCULATE ( QTETOTDEPOT, ALL ( 'Tableau Dispatch'[CODE DEPOT] ) ),
                    [Total dépots],
                    BLANK ()
                )
            VAR TotalAsInt = TRUNC( Total )
            RETURN Total - TotalAsInt 
    )
RETURN
    SUMX ( SummaryTable, [@val] )

Répartition Decimal = IF ( ISINSCOPE('Base Article'[CODE ARTICLE]), [Répartition Decimal Line Item], [Répartition Decimal Total] )

the first 2 are just used in the final calculation, you only need to put the last one into the visual

[SebSchoon1]

I have been able to manage it with this formula

reliquat total = VAR REL= [(1)Nombre décimal Répartition]

                  VAR TOTREL = CALCULATE([(1)Nombre décimal Répartition],REMOVEFILTERS('Tableau Dispatch'[CODE DEPOT]))

                  return if([Répartition]<>BLANK(),TOTREL,BLANK())

But even with this one I cannot verify

If([Reliquat total]<=[Rank],1,blank())

[SebSchoon1]

Hello,

this is impressive coding! thank you for providing me such information.

The visual send me and i'm not able to realize this test

IF([Repartition decimal]<=[Rank],1,0)

it does not give me one.

Maybe i have an error on my Rank measure?

[SebSchoon1]

@johnt75  You really are a god!!