MINX used in Matrix provides incorrect subtotal

[monika_todor]

Hi All,

I'm working on a report where the value I need to see on a subtotal level (for vreme) is the minimum value for the column in the matrix which in this example is -12.901.652, instead of -61.952.817 (which in this case is the summed value of the column).

The challenge is that the MIN value for each time is calculated based on 2 columns in 2 different data sources (which can't be merged): SUM(VkupnoPobaruva[Iznos]) - SUM(VkupnoDolguva[Iznos] hence I can't add a new column instead the measure.

Thank you in advance!

Best,

Monika

[monika_todor]

Thank you Dino for your detailed response, much apprecited!

The thing is that the relationship model is much more complex. Both sources 'VkupnoPobaruva' and 'VkupnoDolguva' are related through DimVreme (for Time) and DimDatum (for date) which means that in none of the tables 'VkupnoPobaruva' and 'VkupnoDolguva' has all the dates and times from the other table, so adding a new column in one of these tables can't solve the problem as there may be rows missing.

[Anonymous]

Hi @monika_todor ,

Can you provide your data model? Replace your original data with test data so that you can avoid data security breaches. I can't reproduce complex models by creating them manually, thanks!

Best Regards,
Dino Tao

[monika_todor · Accepted solution]

Thank you, I found a solution by inserting new data sources and changing the relationships in the model.

[Anonymous]

Hi @monika_todor ,

You mentioned that your two data sources could not be merged, I would like to inquire if there is some sort of one-to-one or one-to-many relationship between VkupnoPobaruva and VkupnoDolguva?
If it exists, you can try this DAX to create a new column in one of the tables:

Column = 
VAR Value2 = 
CALCULATE(
    MAX('VkupnoPobaruva'[Iznos]),
    FILTER(
        'VkupnoPobaruva',
        'VkupnoPobaruva'[time] = 'VkupnoDolguva'[time]
    )
)
RETURN
'VkupnoDolguva'[Iznos] - Value2

Then put this column into the matrix:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.