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:

     

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 

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution Sage
    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
    Icon for Solution Sage rankSolution Sage
    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 MartynRamsden

     

    Thanks so much for the help.

    I have tried your suggestion in a bigger model (where i connect more data) and it works as i want.

     

     

    Thanks also to all who has come upp with suggestion to help me and my report.