Forum Discussion

ScottWill's avatar
ScottWill
Regular Visitor
4 years ago
Solved

Measure values not calculating correctly in a matrix

Measure values not calculating correctly in a matrix

Example:

I have created a few measures in my Inventory table

INV TenderAssignDay

INV ShipAssignDays

INV ShipTenderDays

 

In the results set above you can see the Days are calculating correctly (that is a good thing)

My issue moving forward is this:

I want to create a MATRIX visual that:

COUNTS THE VIN

SUMMERIZE THE DAYS

TOTAL THE COLUMNS

                Below is the MATRIX I tried to create (with no luck)

 

 

 

 

 

 

As you can see below

I can do this in excel with a pivot table

 

 I would like to recreate the same in Pow BI

unfortunately I currently cannot figure out how to do this in Power BI  

 

Please provide any ideas that may help me achieve my goal

 

Example of data sets both excel and power bi provided below

 

DATA in excel

 

 

Power BI detail

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi ScottWill ,

     

    Please try the following formula:

     

    INV ShipAssignDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( [INV ASSIGNDATE], MAX ( inventory[SHIPDATE] ), DAY )
    ),
    [diff]
)
    INV ShipTenderDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( MAX ( inventory[TENDERDATE] ), MAX ( inventory[SHIPDATE] ), DAY )
    ),
    [diff]
)
    INV TenderAssignDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( [INV ASSIGNDATE], MAX ( inventory[TENDERDATE] ), DAY )
    ),
    [diff]
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • ScottWill's avatar
    ScottWill
    Regular Visitor
    4 years ago

    Sky

    Thank You for the reply but not exactly what I was looking for.

    But I will play around with SUMX and Summarize functions.  

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi ScottWill ,

       

      Please try the following formula:

       

      INV ShipAssignDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( [INV ASSIGNDATE], MAX ( inventory[SHIPDATE] ), DAY )
    ),
    [diff]
)
      INV ShipTenderDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( MAX ( inventory[TENDERDATE] ), MAX ( inventory[SHIPDATE] ), DAY )
    ),
    [diff]
)
      INV TenderAssignDays = 
SUMX (
    SUMMARIZE (
        inventory,
        inventory[CUSTOMER CODE],
        inventory[VIN],
        "diff", DATEDIFF ( [INV ASSIGNDATE], MAX ( inventory[TENDERDATE] ), DAY )
    ),
    [diff]
)

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • ScottWill's avatar
        ScottWill
        Regular Visitor
        4 years ago

        v-kkf-msft

        The code examples wowrked, Thank You