Forum Discussion

JSebastianBN's avatar
JSebastianBN
Regular Visitor
7 months ago
Solved

Rows without data showing subtotals

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 ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    7 months ago

    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.

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 months ago

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's avatar
JSebastianBN
Regular Visitor
7 months ago

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's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 months ago

    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's avatar
      JSebastianBN
      Regular Visitor
      7 months ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        7 months ago

        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.