Sum values but dont show blanks

[Wresen]

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 (bigger table)

What i try to archive is like picture below: (dont want the sum on the blank Boxes like the last picture)

I have tried with this code:

Sum Month Box =

Var month = SELECTEDVALUE('Raw Box'[Month])

VAR val =

CALCULATE (

sum ('Raw Box'[Count] );

FILTER (ALL( 'Raw Box') ; 'Raw Box'[Month] =month)

)

return val

But the output looks like this:

is it possible to archive that "forces" the sum on the 1-4 boxs even though they are blank in the bigger table and also dont show Sum on a Blank Box value

/Thanks so much for any help or suggestion.

[parry2k · Accepted solution]

@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 · Accepted solution]

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]

Hi @Wresen 

If you delete the relationship between the two tables, you should get the result you're looking for.

However, doing this could break other parts of your data model or cause other measures to calculate incorrectly, so proceed with caution.

Best regards,

Martyn

[mwegener]

Hi @Wresen,

try this...

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Wresen]

Hi mwegener

i really appriciate the help but i am not sure what you mean ,if i do that i just blanks out the table

I want the "end product" to look like; 

[mwegener]

Hi  @Wresen , 

how about a calculated table?

Table =
CROSSJOIN (
    SUMMARIZE (
        'Raw Box';
        'Raw Box'[Month];
        "Sum Month Box"; 'Raw Box'[Sum Month Box]
    );
    Box
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Wresen]

Hi mwegener

Its needs to be i a measure i am affraid , sorry for not making that clear , but thanks so much for the sugestion.

[parry2k · Accepted solution]

@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

[Wresen]

Hi parry2k 

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]

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

[Wresen]

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

[v-joesh-msft]

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]

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 · Accepted solution]

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 · Accepted solution]

Sorry @Wresen ,

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

[Wresen]

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.