Forum Discussion

Rhodes's avatar
Rhodes
Frequent Visitor
6 years ago
Solved

Need Help calculating % Diff of multiple events and returning smallest difference in dax

Greetings.

 

I have a table in the following format:

 

I am attempting to create a table that looks like the one shown below but instead of the row subtotal it would show the % difference of the values in the rows above. 

 
 
 

I made a workaround for the individual metershed report by having the model and observed values in a table visual and the corresponding % difference values in separate card visuals. 

 

 

However, this workaround solution doesn't work well when trying to show a summary of the % difference for all the meters. Any suggestions on how to create a table with these know values and the calculated % difference values would be appreciated. Then from there, I would like to have a measure that returns the lowest % difference for a given metershed over the 3 calibration events.

 

Thanks,

Steven

Steven

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this measure:

    Measure = 
var a = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Data Type]="Event AverageModel Flow"&&'Table'[Calibration Event] in FILTERS('Table'[Calibration Event])&&'Table'[Metershed] in FILTERS('Table'[Metershed])))
var b = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Data Type]="Event AverageObserved Flow"&&'Table'[Calibration Event] in FILTERS('Table'[Calibration Event])&&'Table'[Metershed] in FILTERS('Table'[Metershed])))
return
IF(ISINSCOPE('Table'[Data Type]),FORMAT(SUM('Table'[Value]),"Fixed"),FORMAT((b-a)/a,"Percent"))

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

2 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    Please try this measure:

    Measure = 
var a = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Data Type]="Event AverageModel Flow"&&'Table'[Calibration Event] in FILTERS('Table'[Calibration Event])&&'Table'[Metershed] in FILTERS('Table'[Metershed])))
var b = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Data Type]="Event AverageObserved Flow"&&'Table'[Calibration Event] in FILTERS('Table'[Calibration Event])&&'Table'[Metershed] in FILTERS('Table'[Metershed])))
return
IF(ISINSCOPE('Table'[Data Type]),FORMAT(SUM('Table'[Value]),"Fixed"),FORMAT((b-a)/a,"Percent"))

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi