Greetings

@Barbara Forsman:

Once again, great post - great example of how to measure granularity within a measure!

Below are my:

- Final Results
- A few Caviots
- Final Equation

1) Final Results:

You can see that the granularity between the 'OldEquation' and the 'NewEquation' are consistent*, albeit at the grand total level where the New equation sums all of the above metrics (image one below).

*A few minor items will be covered in section two

IMAGE ONE

2) Caviots

There are a few Quote numbers that have the same lowest BW and max Term (See image 2.1 below - quote numbers on left)

Image 2.2 details one example - Quote Number "QUO-02226-Y2S3S9" has two MRC codes for the lowest BW & MAX Term.

What this means is that my new equation will add these values together and add them to the report. The old report would not show these quote number MRC values at all (reference image 2.1). A business decision needs to be made as to include these values or not

IMAGE 2.1

IMAGE 2.2

3) FINAL EQUATION

With all these factors in mind, I am ready to present the final equation. The PBI file has been attached for your reference.

**NewEquation** =

VAR MinBW =

MIN (

Table1[BW] )

VAR MaxT =

MAX (

Table1[Term] )

VAR StandardEquation =

CALCULATE (

SUM (

Table1[MRC] ),

Table1[BW] = MinBW,

Table1[Term] = MaxT )

VAR Total_Equation =

VAR Final_Table =

GENERATE (

Table1,

ROW (

"EQUATION",

VAR ONE =

CALCULATE (

MIN (

Table1[BW] ),

FILTER ( Table1,

Table1[Quote Number] =

EARLIER (

Table1[Quote Number] ) )

)

VAR TWO =

CALCULATE (

MAX (

Table1[Term] ),

FILTER ( Table1,

Table1[Quote Number] =

EARLIER (

Table1[Quote Number] ) )

)

VAR EQUATION =

IF (

Table1[BW] = ONE

&& Table1[TERM] = TWO,

Table1[MRC] )

RETURN EQUATION

)

)

RETURN CALCULATE (

SUMX ( Final_Table,

[EQUATION] ) )

VAR YEAR =

SELECTEDVALUE (

'Calendar'[Year] )

VAR TOTAL_TYPE =

COUNTROWS (

VALUES (

'Calendar'[Year] ) )

VAR Total_Indicator =

CALCULATE (

COUNTROWS ( Table1 ),

'Calendar'[Year] =

YEAR,

ALLSELECTED ( Table1 )

)

RETURN IF (

COUNTROWS ( Table1 ) = Total_Indicator

|| ( TOTAL_TYPE >

1 ),

Total_Equation,

StandardEquation

)

--END OF FORMULA

Thanks!

William

#DAX #PowerBI #Granularity #Variables

------------------------------

William Rodriguez

Business Analyst

------------------------------

Original Message:

Sent: 07-24-2018 04:56 PM

From: Barbara Forsman

Subject: Measure does not total

Thank you! I will see if I can apply your logic to my data.

In the meantime I've uploaded scrubbed data file here

I appreciate your help!!

------------------------------

Barbara Forsman

MIS

NebraskaLink

Lincoln NE

4028171425

Original Message:

Sent: 07-24-2018 04:31 PM

From: William Rodriguez

Subject: Measure does not total

Greetings @Barbara Forsman:

Great question!

The majority of the question resolves deeper than just the question - we really need to see how your data is structured.

From what I can tell, the following is happening:

The issue is in SelectedValue. SelectedValue is 'syntax sugar' for 'IF( HASONEVALUE() , VALUES())'

Translation:

- If I (the context evaluation) have one value, then return that value
- Else, return blank

What is happening is that in your matrix, the 'evaluation context' is on the row level. So the equation is saying:

- For 'Quote Number' X, find the respective MRC number filtered by the the lowest BW & Max Term (this is incorporating your variables)
- On the Grand total field, there are multiple MRC numbers, so return blank.

You can correct this by doing something like the following:

VAR A

VAR B

RETURN

IF (

Countrows (Table) = Countrows (ALL ( Table) ) , /*This check the granularity - if true, then all fields are shown*/

SUMX ( Equation Over Table )

Equation

)

Something along those lines should help - if you can upload a 'dummy' data table, that would be great to give a more exact equation / resolution.

Hope this helps!

William

------------------------------

William Rodriguez

Business Analyst

Original Message:

Sent: 07-24-2018 11:53 AM

From: Barbara Forsman

Subject: Measure does not total

I have a measure that finds the smallest bandwidth and longest term for a quote and displays the MRC. But the MRC does not total in a matrix.

This is the measure I'm using:

MinBWMaxT =

var minBWValue = min(BWT_MRC[BW])

var maxTermValue = max(BWT_MRC[Term])

return CALCULATE(SELECTEDVALUE(BWT_MRC[MRC]), BWT_MRC[BW] = minBWValue, BWT_MRC[Term] = maxTermValue)

My matrix displays the data correctly, but without the total.

I have verified the MRC column is a number in my table.

Do I need to alter my measure, or somehow create a new table based on the measure and *then* total the MRC?

Any help will be much appreciated!

------------------------------

Barbara Forsman

MIS

NebraskaLink

Lincoln NE

4028171425

------------------------------