Issue to sum a Measure

[Anonymous]

Hello,

I am building a PBI report, in which I created a DAX measure that simulated a rates in a selected past period.
The user selects a period of refrence (1 month) the report displays the total actual cost for the different services occuring that month. Then, they select a past period to simulate the rates of the refrence period. The goal is to know how much would they have paid if these services occured in the past.

DAX Code:

SimulatedRate =

VAR SelectedDate =

    IF(

        ISBLANK(MIN(ContractDate[Start of Month].[Date])),

        TODAY(),  -- If no date is selected, return today's date

        DATE(

            YEAR(MIN(ContractDate[Start of Month].[Date])),

            MONTH(MIN(ContractDate[Start of Month].[Date])),

            DAY(MIN(ContractDate[Start of Month].[Date]))

        )  -- Extract year, month, day to remove the time part

    )

--- SelectedDate is what the user selected in a slicer filter on the report, if nothing is selected it's today's date.

VAR po_code = -- ID of the used contract

VAR actual_cost = -- actual cost

VAR rate_found_flag2_Test = -- check if the contract existed in SelectedDate 

VAR service_code = -- code of the service

VAR SimulatedRate = -- 1st attempt to simulate the rate

VAR RateService =  -- simulates the rate for a specific type of services

VAR Test1 = IF(ISBLANK(RateService), actual_cost, RateService)

VAR test = IF(ISBLANK(SimulatedRate), Test1, SimulatedRate)

VAR result =

    IF(

        rate_found_flag2_Test = "SPOT",

        actual_cost,

        IF(rate_found_flag2_Test = "RATE NOT FOUND", actual_cost, test)

    )

RETURN

    SUMX(OBT_VS_COST_PER_UNIT_CONTROLLING_delivered_ordered_mvts_flagged, result)



The relationship between services table and contracts table is many-to-one.

The issue is: When I disply the simulated rates individually in a table visual, they are correct. However, when I try to sum them in a column visual ar card visual, the sum is incorrect (it shows the total actual cost).

I am stuck, please help !

[Anonymous]

Hi @Anonymous 

Could you please provide sample data that completely covers of your problem and expected results?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

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

[Anonymous]

Hi @Anonymous ,

Here is a sample from the result I want to get: data sample 
Now, when it comes to displaying the result in a table visual, all good I get the result I want. However, the totals are incorrectly calculated (the actual cost and the simulated rate respective sums are equal).
Same problem using column visuals.

Any advice ?

Thanks,
Olfa

[Anonymous]

Hi @Anonymous 

Maybe my description is not clear enough for you to misunderstand, I would like you to provide the sample data used in this DAX so that we can figure out the problem and propose a solution more easily. It would be nice if you could provide the pbix file.(In the data provided please remember to remove privacy)

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

[Kedar_Pande]

@Anonymous 

Modify measure usig CALCULATE to modify the filter context

SimulatedRate =
VAR SelectedDate =
IF(
ISBLANK(MIN(ContractDate[Start of Month].[Date])),
TODAY(), 
MIN(ContractDate[Start of Month].[Date])
)

VAR po_code = -- Define your po_code logic here
VAR actual_cost = -- Define your actual_cost logic here
VAR rate_found_flag2_Test = -- Define your rate_found_flag logic here
VAR service_code = -- Define your service_code logic here

-- Logic to simulate rate
VAR SimulatedRate =
-- Add your logic here to simulate rates based on the selected date

VAR RateService =
-- Add logic here to calculate the service rates

VAR Test1 = IF(ISBLANK(RateService), actual_cost, RateService)
VAR test = IF(ISBLANK(SimulatedRate), Test1, SimulatedRate)

-- Final result based on conditions
RETURN
CALCULATE(
SUMX(
OBT_VS_COST_PER_UNIT_CONTROLLING_delivered_ordered_mvts_flagged,
IF(
rate_found_flag2_Test = "SPOT",
actual_cost,
IF(rate_found_flag2_Test = "RATE NOT FOUND", actual_cost, test)
)
),
FILTER(
OBT_VS_COST_PER_UNIT_CONTROLLING_delivered_ordered_mvts_flagged,
-- Ensure this filter reflects your intended context; e.g. only relevant rows based on date or service code
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Hi @Kedar_Pande ,

Unfortenately, this didn't solve my problem. 

Thank you though.

[johnt75]

Variables in DAX aren't really variable, they are constants - they are only evaluated once. So when your result variable is calculated once, and then that value is used in the SUMX.

You need to wrap the calculation of result inside the SUMX. Depending on whether the po_code and other variables are dependent on the row context from OBT_VS_COST_PER_UNIT_CONTROLLING_delivered_ordered_mvts_flagged, you could rewrite it to something like

SimulatedRate =
VAR SelectedDate =
    IF(
        ISBLANK(MIN(ContractDate[Start of Month].[Date])),
        TODAY(),  -- If no date is selected, return today's date
        DATE(
            YEAR(MIN(ContractDate[Start of Month].[Date])),
            MONTH(MIN(ContractDate[Start of Month].[Date])),
            DAY(MIN(ContractDate[Start of Month].[Date]))
        )  -- Extract year, month, day to remove the time part
    )
--- SelectedDate is what the user selected in a slicer filter on the report, if nothing is selected it's today's date.
SUMX(OBT_VS_COST_PER_UNIT_CONTROLLING_delivered_ordered_mvts_flagged,
VAR po_code = 1-- ID of the used contract
VAR actual_cost = 1-- actual cost
VAR rate_found_flag2_Test = 1-- check if the contract existed in SelectedDate 
VAR service_code = 1-- code of the service

VAR SimulatedRate = -- 1st attempt to simulate the rate

VAR RateService =  -- simulates the rate for a specific type of services

VAR Test1 = IF(ISBLANK(RateService), actual_cost, RateService)
VAR test = IF(ISBLANK(SimulatedRate), Test1, SimulatedRate)
VAR result =
    IF(
        rate_found_flag2_Test = "SPOT",
        actual_cost,
        IF(rate_found_flag2_Test = "RATE NOT FOUND", actual_cost, test)
    )

RETURN
     result
)

[Anonymous]

Hello Johnt75,

Unfortenately, this did not work.

Thank you anyway ^^