Forum Discussion

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
6 years ago
Solved

Sum values but dont show blanks

Hi  I have a little problem that i have tried to solve , not sure if its possible. I have two tables.   They have an relationship between Box - Box , single (right to the left table (bi...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Wresen here is the measure and what you need on filter

     

    Measure = CALCULATE( COUNTROWS ( 'Box Tran' ), ALLEXCEPT( 'Box Tran', 'Box Tran'[Month] ) )

     

     

    In table visual, drop Month and Box from (box table) and the measure above. and you will get this

     

  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Wresen 

     

    Try using ALLNOBLANKROW in place of ALL, as follows:

     

    Runningcount =
SUMX (
    FILTER (
        ALLNOBLANKROW ( 'Box Table'[Box] ),
        'Box Table'[Box]
            <= MAX ( 'Box Table'[Box] )
    ),
    [Amount]
)
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Sorry Wresen ,

     

    I forgot to mention that you'll also need to apply a filter on the 'Box' column in the visual:

     

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
6 years ago

Hi  

 

Thanks so much for that code , is it possible to modify so i do not need to the filter thing in the filters.

My tought is to use 2 more measures in this report , one measurethat count the nr of boxes that have box 1-4 and also a measure  of Cumulative count of box 1-4.

The problem i will face if i have values at the blank Box and "just filter them away" the resulat will be wrong since the measures will ignore the filter we applyed.

 

I hope i make sense in what i am trying to archive 🙂

 

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Wresen send the pbix file with sample data and expected result.  Remove any sensitive information before sharing.

  • Wresen's avatar
    Wresen
    Icon for Post Patron rankPost Patron
    6 years ago

    Stupid question but how do i attach i file , cant find the option to do that (just photos or Insert Video)

    • v-joesh-msft's avatar
      v-joesh-msft
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Hi Wresen ,

      You need to have a cloud storage such as Onedrive or Google Drive where you can put your file. After that, you create a shareable link that you can insert in your post enabling people to download the pbix.

      Best Regards,

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

      • Wresen's avatar
        Wresen
        Icon for Post Patron rankPost Patron
        6 years ago

        Hi

        I hop i have done it correct , the file is in "Box sum" bellow

         

        What i am trying to do is: 

        but i get :

         

        (What i want is the measure's to ignorne the "blank" box in the calculations. I needs to be in measures and i need to have the relationship since hoppfully i will conect other data to the "Box Table")

         

        (Credit to parry2k for the Totalsum measure)

         

         Box sum