Incorrect Total after what-if parameter

DAX DAX Calculations
1

Hi all,

I am building below matrix and need to convert the SqFt to Capacity (CufT) (SqFt * Density).

image

As 2 warehouses have different Density, and I write below DAX.

However, I still cannot figure out how to get the correct total capacity.

Can anyone help? Thanks.

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 Total=GSW+Oneill
Return
if(SELECTEDVALUE(Warehouse[Warehouse])=“GSW”,GSW,if(SELECTEDVALUE(Warehouse[Warehouse])=“Oneill GA”,Oneill,Total))

Sorry for I cannot upload the PBI file now as the file size is too large

1 Like
2

Hi @chiu2003, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi @chiu2003,
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.

image
image1263×510 19.8 KB

Regards,

Incorrect Total after what-if parameter_JAFP.pbix (392.8 KB)

1 Like
4

Hi @chiu2003, did the response provided by @jafernandezpuga help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Hi @chiu2003, we noticed that no response was received from you with regard to our post last Oct 7th.

We just want to check if you still need further help with this post.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @chiu2003, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box