How to Calculate Total after using Parameters?

DAX DAX Calculations
1

Hi all,

I got below report and cannot calculate a correct grand total.

Some logic for calculation
- Density is a numeric parameter
- Each Warehouse has own Density Value
- CuFt = SqFt * Density

I am using below DAX for calculating CuFt for each warehouse, but I still cannot return 1,179,800 for Table total (570,000+ 380,800+102,000+102,000+25,000) instead of Depend Density

Can anyone help?

DAX Code

Capacity (CuFt) =

VAR GSW=if(SELECTEDVALUE(Warehouse[Warehouse])=“GSW”,‘WH Capacity Measurement’[WH CuFt Capacity]*‘Density - GSW’[Density - GSW Value],0)

VAR Oneill=if(SELECTEDVALUE(Warehouse[Warehouse])=“Oneill GA”,‘WH Capacity Measurement’[WH CuFt Capacity]*‘Density - ONLGA2’[Density - ONLGA2 Value],0)

VAR EOSCA=if(SELECTEDVALUE(Warehouse[Warehouse])=“EOSCA”,‘WH Capacity Measurement’[WH CuFt Capacity]*‘Density - EOSCA’[Density - EOSCA Value],0)

VAR EOSTX=if(SELECTEDVALUE(Warehouse[Warehouse])=“EOSTX”,‘WH Capacity Measurement’[WH CuFt Capacity]*‘Density - EOSTX’[Density - EOSTX Value],0)

VAR Irvine=if(SELECTEDVALUE(Warehouse[Warehouse])=“Irvine”,‘WH Capacity Measurement’[WH CuFt Capacity]*‘Density - Irvine’[Density - Irvine Value],0)

Return

if(SELECTEDVALUE(Warehouse[Warehouse])=“GSW”,GSW,

if(SELECTEDVALUE(Warehouse[Warehouse])=“Oneill GA”,Oneill,

if(SELECTEDVALUE(Warehouse[Warehouse])=“EOSCA”,EOSCA,

if(SELECTEDVALUE(Warehouse[Warehouse])=“EOSTX”,EOSTX,

if(SELECTEDVALUE(Warehouse[Warehouse])=“Irvine”,Irvine,

“Depend Density”)))))

chiu2003_0-1687259337533
chiu2003_0-1687259337533968×661 34.8 KB

2

Hello @chiu2003,

Thank you for posting your question on visualizing warehouse capacity/quantity on hand estimation using Power BI. Nine months ago in 2022, you sought help with an identical question.

An Enterprise DNA Expert named José Antonio Fernández Puga, who can be found at @jafernandezpuga, has created a Power BI solution for you. Although lacking a sample Power BI Desktop file, he has worked hard to provide a capable solution, which I will explain below.

@jafernandezpuga Power BI Solution:
Incorrect Total after what-if parameter_JAFP.pbix (392.8 KB)

In his reply as you will see, he offered the following guidance and advice:

Thanks very much for post your query into the forum.
To fix the calculation of the measure in the totals you can create the following measures:

Capacity (CuFt) =

VAR _Density_GWS = [Density - GWS Value]

VAR _Density_Oneill_GA = [Density - Oneill GA Value]

VAR _Current_Warehouse = SELECTEDVALUE( Warehouse[Wharehouse] )

VAR _Current_SqFt = SELECTEDVALUE( Warehouse[SqFt] )

VAR _Density = SWITCH(

    TRUE(),

    _Current_Warehouse = "GSW", _Density_GWS,

    _Current_Warehouse = "Oneill GA", _Density_Oneill_GA

)

VAR _Result = _Current_SqFt * _Density

RETURN

_Result


Capacity (CuFt) Total =

VAR _VirtualTable =

ADDCOLUMNS(

    VALUES( Warehouse[Wharehouse] ),

        "@Capacity (CuFt)", [Capacity (CuFt)]

)

VAR _Result = IF(

    HASONEVALUE( Warehouse[Wharehouse] ),

    [Capacity (CuFt)],

    SUMX( _VirtualTable, [@Capacity (CuFt)] )

)

RETURN

_Result

We create the measure for the records in the table and another measure for the totals that sums all the values of the measure for each individual record.

d89e2358d21b5a02d5d0859c28510a0b2ca5862c_2_690x278

–

Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a like.

1 Like