Measure if another column then return blank?

[Anonymous]

So I have a fact table with data by ID for different metrics. I have a measure that calculates ratios based on the fact table data. This fact table also has a column for 2020 Actuals, 2020 Budget, and 2020 Variance to Budget called Year Type.

I have another table that groups the ID's by Country.

I'm creating a matrix with Country as the top level in columns and Year Type as the second level in columns. Then all my measure ratios are values displayed as rows.

So it would show USA as top level column, and then the column level underneath would be 2020 Actuals, 2020 Budget, and 2020 Variance to Budget. 

This works fine, but the variance to budget is misleading. The calculation works correctly, but variance should be calculated differently. An example of my data is below:

 

So you can see in the ratio column that the measure would calculate correctly for the variance (Earnings/Price), however, the result isn't correct. It should be 25%-10%.

I need my data structured as Country in Level 1 for Columns and Year Type for level 2 in a matrix. I can either try to create a measure that would do the variance as laid out above (25%-10%), or if I could just leave the result as blank because the current result is a little misleading.

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous]

@v-diye-msft @sanimesa 

I created a very simple data model in Power BI based on the dataset in my original post.

The measure formula is as follows:

Measure = DIVIDE(CALCULATE(SUM(Sheet1[Amount]),FILTER('Sheet1','Sheet1'[Account]="Earnings")),CALCULATE(SUM(Sheet1[Amount]),FILTER('Sheet1','Sheet1'[Account]="Price")),0)

So as described in the original post, as my variance data comes in as it's own dimension of actuals - budget, when that data is used in the measure above, it produces an incorrect result. As shown in the original post, I would want the variance to somehow show the differenence between actuals and budget (25%-10%) rather than recalculating based on the data, since the data already is actuals - budget. If it's not possible to show the result as 25%-10%, if I could somehow show a blank instead as the current 6% that returns is misleading. I need the column levels in the matrix to remain.

[sanimesa · Accepted solution]

@Anonymous You can achieve this by modifying your measure as follows:

Measure 2 = 
VAR selyearType = SELECTEDVALUE(Sheet1[Year Type])
RETURN IF(CONTAINSSTRING(selyearType, "Variance"), BLANK(), DIVIDE(CALCULATE(SUM(Sheet1[Amount]),FILTER('Sheet1','Sheet1'[Account]="Earnings")),CALCULATE(SUM(Sheet1[Amount]),FILTER('Sheet1','Sheet1'[Account]="Price")),0))

You have to select the Year Type under columns and specify show values with no data. (See diagrams.)

[sanimesa]

@Anonymous Can you please post the desired picture? 

It should be possible to create a measure that will either produce a ratio, or a blank depending on the context in the martix.