Forum Discussion

MMPPCP's avatar
MMPPCP
New Member
3 years ago

Result table for combined calculation steps

Dear Community,

 

I would like to aks for your support in the following topic.

 

Input data:

TABLE1: transport data of multiple transport relations 

 

TABLE 2 

- Truck type

- Truck dimensions (capacity in gross weight kg)

 

 

MEASURE 1: average utilization % (which is the avg. gross weight divided by the max gross weight capcity of the given truck type)

- measure created with a "general" formula:

- average utilization % = DIVIDE(AVERAGE('Transport_data'[Gross_weight]), CALCULATE(AVERAGE('Truck type'[Gross weight])))

 

MEASURE 2: average utilization% w. alternative weight 1 (transport with alternative weight)

MEASURE 3: average utilization% w. alternative weight 2 (transport with alternative weight)

 

The combination of table truck type and the measures above result the following table. It works if there is one selected transport relation.

 

In addition I have a checking measure to each utilization scenario (utilization checking 1-3), which checks if alternative utilization is bigger than actual and the utilization is max 115% --> if yes result is 1, if false result is zero. This gives the flag for the values as conditional formatting in the blue table (see screenshot above).

 

QUESTION: I need a table which shows, whether any of the three utilization values have the flag  - i.e. any of the checking formulas give the result of 1 - but as a summary table not only for the selected transport relation but for all of them (see sample below)

Transport ID1Any utilization scenario with flag?
Transport 1yes/no

 

Your support is highly appreciated in advance!

 

Best regards,

Peter 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MMPPCP,

    Did you means these measure formulas calculate different results when you used them to handle aggregated records? If that is the case, you can take a look at the follow blog about measure total which mention multiple aggregated calculation with measure formulas:

    Measure Totals, The Final Word 

    In addition, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

    • MMPPCP's avatar
      MMPPCP
      New Member
      3 years ago

      Dear Anonymous 

       

      thank you for reacting to my question. Please find a pbix file which reproduces my issue here: https://drive.google.com/file/d/1mzERu58uBr3NlvcBAoaNyQiqxQQp8SNn/view?usp=sharing

       

      It is about transport management and there is an overview table. column Route ID is the main key (Route ID = Transport ID in my original message).

       

       

      If you select an ID (i.e. an item in column Route ID) the table below is activated and it checks, what would be the average utilization of trucks, if there is a truck type and/or delivery frequency change.

      • Average utilization% (w. actual frequency) = utilization scenarios with the actual delivery frequency
      • Average utilization FQ -1x/week = utilization scenarios with reduced frequency (-1 truck)
      • Average utilization FQ -2x/weekutilization scenarios with reduced frequency (-2 truck)

       

      If there is a case, where the average utilization would be better then the actual one (and below the 115% threshold), then it gets a yellow flag. If a reduced frequency case would be 0 or below (e.g. avg. frequency is 2 truck, then the FQ-2 scenario (e.g. frequency reduction with minus 2 truck) shows "infinity".

       

      In the overview table I would like to see, whether there is a "flag case" for the given route. Columns (and measures) check_ut FQ, check_ut FQ-1, check_ut FQ-2 supports check_ut FQ SUM  to show this info - however it is not working correctly and this is the issue.

       

      E.g. for Route 5 it does not show any flag in the overview table, although there is one or in case of Route 6 it does not show that there is a flag with the current utilization scenario as well.

       

      So the main point os to have a relaible and correct feedback column (check_ut FQ SUM) about good cases at the first view without clicking through all the IDs.

       

      Your help and support is highly appreciated!

       

      Best regards,

      Peter