Matrix showing more rows & filters seems not working

[Majidbhatti]

Hi

Table 1: This is the data set

Table 2: This is what is required and matrix is respecting the page filters and showing 3 correct forecasts.
Now for the BLANKS i want to replace it with ACTUALS corresponding to its month but when i do the formula as below it brings more forecasts and not respecting page filters (see TABLE 3)

Actual-Forcast ('000) (S) = 
IF(ISBLANK([Forecast ('000) (S) (Cur)]) , [Actuals ('000) (S) (Cur)] , [Forecast ('000) (S) (Cur)])

[Anonymous]

Hi @Majidbhatti 

We use Trans_Type=F20210301 to analyze the returned data .In your picture 1 ,the values for Jan and Feb are null .You want to use the values for Actuals (A) to fill Trans_Type=F20210301 , so the value below is correct .As for the value of other months(Jul,Aug,…), it shows a null value , because the value of A in these months is empty.

For your picture 2 and picture 3, the Trans_Type you provide are different , I don’t know how to compare .

If it is possible ,could you provide a pbix file(without sensitive information) to me to analyze the problem ?Looking forward to your reply .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Majidbhatti]

Hi @Anonymous 

I appreciate that you are trying to help but since its Financial data of a company i cannot share. Let me make my question more simplier.

I have a dataset which looks like this

And the Page has COMPANY & YEAR Filter. And i need the result matrix to look like below

Hope i made it simplier.

[Anonymous]

Hi @Majidbhatti 

I don't particularly understand the problem you are talking about .So table 1 is your original data and table 2 is your expected result ?

What is the setting of your the page filters ? What is your ACTUALS corresponding to its month ? What is your Forecast in matrix ? Are they regular values, measures or calculated columns?

Looking forward to your detailed reply .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Majidbhatti]

Hi @Anonymous 

Find details below

The above matrix was has regular values & date table.

Now what i want is to show Forecast lines e.g F20210101 etc. only and the BLANK cells in Forecast lines should be replaced by Actuals (A) values. What i am trying to show is the comparisons of various forecasts with Actuals.

So below is the RESULT i want where Actuals (Green box) with Forecast (BLUE) 

To achive the above, i created below measure and replaced the VALUE field in Matrix

Actual-Forcast ('000) (S) = 
IF(ISBLANK([Forecast ('000) (S) (Cur)]) , [Actuals ('000) (S) (Cur)] , [Forecast ('000) (S) (Cur)])

But after doing so i am getting below matrix, where its bringing the forecast of OTHER companies & Years and its not following the page filter of company and year.

Hope i explained in detail, let me know if you need more details.