Rows without data showing subtotals

[JSebastianBN]

Hi, below you can see my example (below the data):

The column "Sum of SP" has values at row values F and it computes the subtotals at row C levels and E levels.

The column "Sum of STSP" only has values only at row E levels, it does not have values in C or F rows. However, it replicates the E values values at the sublevels C and F. How can "Sum of STSP" show empty values for rows F and C? 

Thanks!

Below the data:

Table Capability:

Capability	Epic
C1	E1
C2	E1
C3	E2
C4	E2
C5	E2

Table EpicSTSP

Epic	STSP
E1	20
E2	0

Table Feature

Features	Capability	SP
F1	C1	10
F2	C2	5
F3		2
F4	C3	1
F5	C1	4
F6	C2	3

Model image

[ryan_mayu]

@JSebastianBN 

We can use measure to show C as blank. However, it's better to find out the root cause.

could you pls provide the sample data , not the matrix visual

[JSebastianBN]

Hi, just added the data to the original post. Thanks!

[ryan_mayu]

@JSebastianBN 

the data model is not very good. However, I am familiar to your business. So I can't tell you how to improve this. 

To solve this problem, you can try to create a measure

Measure = if(ISFILTERED(Feature[Features]),blank(),sum(EpicSTSP[STSP]))

pls see the attachment below

[JSebastianBN]

Thanks for your help Ryan 🙂 Your proposal works.

I wonder however if this can be fixed somehow from the Model itself. I've a couple of questions:

1) Can this be solved from the Model itself? I do not understand why PowerBI should automatically fill cells where there is no value.

2) You mention that the model is not very good. Do you have recommendations that I can follow to improve it?

Hope you can help men. Thanks for your time!

[ryan_mayu]

@JSebastianBN 

In most cases, we start our data aggregation from the lowest level. 

e.g. 

Level 1: A

Levle 2: A1,A2

A1=10,A2=20, then A = 10+20=30

However, in your scenario, the value is assigned to higher level and skipped the lower level.

Then,  the bottom-level data automatically inherits the values from the higher-level.

That's not what we do usually.  And like what I mentioned before, since I am not familar to your business, so I am sorry that I can't tell you where you can improve in this scenario.

[JSebastianBN]

Thanks Ryan!

The thing is the two columns come from different places, and I want to compare the totals C and E between the two columns.

In the case of the first column (Sum of STSP), we've only the values from level C, which then provide the upper level E.

The second column (Sum of SP) is different, here we do have values for the lowest level F, which then adds up to C which then adds up to E.

Maybe this helps providing clarity, but otherswise it's ok you already helped a lot 🙂 Thanks!

[ryan_mayu · Accepted solution]

@JSebastianBN 

You are welcome.

If you want to compare STSP with SP, maybe you can remove the third level, since the comparison at that level is meaningless.

If my solution works, please accept it and appreciate your kudos as well.

[cengizhanarslan]

Looks like you have some problem within your relations in your model. You could use a measure like below as an example but I think it won't be a perfect solution here. It would be the best if you share a .pbix example.

STSP =
IF (
    ISINSCOPE ( 'Dim'[C] ) || ISINSCOPE ( 'Dim'[F] ),
    BLANK(),
    [Sum of STSP]
)

[JSebastianBN]

Hi, I just added the data to the post. Thanks!

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here