Total Min/Max measure where and keep the value static

[Bmejia]

Hi, 
I have a measure that calculates the minimum/Max,  that I need to remain static no change when a date(Filter) is applied, but I also need to be able to add the "Total" value, currently I get zero for the total.  I have added hasonevalue and summarized data to get the "Total" but it excludes anything withing the date range that does not have a value.

I know this will give me all the value where they be static, but total will be zero.

Inventory W Not Total =

CALCULATE(

    MIN(Purchase_Sold[Available Physical]),All('Calendar'[Date]))

But when I add the "Total",  it excludes the values that are not within the calendar date range.  I have used for "HASONEVALUE" and removed and it still excludes the values.

Inventory W Total =

IF(

    HASONEVALUE( 'Purchase_Sold'[ITEMID]),

    CALCULATE(MAX( 'Purchase_Sold'[Available Physical] ),ALL('Calendar'[Date])),

    SUMX(

        SUMMARIZE( 'Purchase_Sold', 'Purchase_Sold'[ITEMID], "Invt", CALCULATE(MAX( 'Purchase_Sold'[Available Physical] ),ALL('Calendar'[Date]))),

        [Invt]

    )

)

Example: I want to be able to see everything that is in Inventory like Inventory W no total (2nd Column) but with total of all values.

[lbendlin · Accepted solution]

cut the connection between the tables and then use TREATAS in your measures to transfer the filter as needed.

[Bmejia]

Thank You!!, Ashish & Ibendlin,

Both solutions seem to work, as long as there is a transaction in the date range. 

Ibendlin, I created a summarize table and cut the relationship, In order to keep the date table connected.  I cannot remove the relationship because there are other measures that are linked and need to add the month and year as a column as well.

Sample of new issue:

[Bmejia]

Thank you both!!
I believe I was able to fix the issue by generating a independent table with the products then calling it on my measure as below "Manual_List[Product].  Using the Treatas function as Lbendlin had suggested for the date table.

Updated Measure=

CALCULATE(
IF(
HASONEVALUE( 'Invt_Aval'[Product]),
CALCULATE(MIN( Invt_Aval[Available] )),
SUMX(
SUMMARIZE( 'Invt_Aval', 'Invt_Aval'[Product], "Invt", CALCULATE(MIN('Invt_Aval'[Available]))),
[Invt]
)
)
,TREATAS(VALUES(Manual_Items_List[Product]),Invt_Aval[Product]))

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[lbendlin · Accepted solution]

cut the connection between the tables and then use TREATAS in your measures to transfer the filter as needed.

[krishnakanth240]

Hi @Bmejia 

Please share the sample data of table in text format

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...