Calculation is good but only with all the columns

Power BI
1

Hi everyone,

I have an issue with this Power BI file. Unfortunatly, I cannot share the file with you. Just too much work to remove company data. But I have hope that you can give me somme ideas to resolve my problem.

As you can see by the image, everything works. My calculations are correct. But I only need the part in green. I do not want the orange part but when I remove any of the colums in the orange area then my CRL Volume Class Finale is wrong. :frowning:

So all the columns need to be there to have the correct result.

What can I do ?

If it would be a database, I would create myself a materialized view (basically write (export) the results and then go back and just retrieve the 3 columns that i need. How can I do this in Power BI ?Data volume is not very big, so it would be OK to do this with Power BI.

Quick overview,

I have 3 tables VLPC, NCA, CRL (desc in lower left of the image)

The measure
Volume Class Finale = CALCULATE(CRL[Sum Volume]) - CALCULATE(CRL[Volume Diff Jour Avant moins jour])

Basicaly, I’m calulating the SUM of Volume Minus the Volume Difference with the day before. (A little more complex but …)

How can I remove the 2 orange columns and still keep my correct results. (BTW, it’s been a lot of work for me to cet to this part, with all my correct values etc. So I’m close but not there yet)

Eric (Montreal, Canada)

april15
april151413×914 331 KB

1 Like
2

@ericet Use SUMMARIZE to compute the result on the right granularity.

CRL Volumne Classe Finale =
SUMX (
    CALCULATETABLE (
        SUMMARIZE (
            Table,
            Table[VLPC DateLivraisonPrevue],
            Table[VLPC Classe],
            Table[CRL DateLivraison],
            Table[NCA Classe]
        ),
        ALLSELECTED ( Table[Type Requisiton] )
    ),
    [Sum Volume] - [Volume Diff Jour Avant moins jour]
)
4 Likes
3

Hi @ericet. As with most things in Power BI, there can be different ways to do the same thing. Not sure if this “hack” would help in your scenario, but you could possibly change the formatting of your “orange” columns as follows:

  • set font colour = white
  • set word wrap (for value and headers) = off
  • set column width to minimum (drag with mouse)

Hope this helps.
Greg

2 Likes
4

More than one way to skin a cat! :cat:

5

Thank you for this AntrikshSharma,

I am using the real table names and column here.
This is your code and it works, meaning the calculations are correct.
But I have the same problem, when I remove the 2 columns (Orange) then same result.

I did a measure, should I do something else ?

ASharma =
SUMX(
CALCULATETABLE(
SUMMARIZE(
VolumeLendemainParClasse
, VolumeLendemainParClasse[DateLivraisonPrevue]
, VolumeLendemainParClasse[NoClasseApprov]
),
ALLSELECTED( VolumeLendemainParClasse[NoClasseApprov])
),
[Sum Volume] - [Volume Diff Jour Avant moins jour]
)

A15_2
A15_21249×704 264 KB

But when I remove the Orange columns it’s the same problem.
BTW I always use Nov 11 Class 1 2 522 098 too quickly see my
results, if that value works all others are correct.

A15_3
A15_31272×713 197 KB

6

Greg,

I like it ! I might use this trick.

But my ultimate goal is to figure it out the correct way. Like you or Sam McKay would do it, if it was your project. Also, I would like to use some of these values to have a weekly totals. But one problem at a time.

But learned a new trick for sure :wink: Thanks Greg

A15_4
A15_41262×714 199 KB

1 Like
7

@ericet what is the code of the following measures?

[Sum Volume] - [Volume Diff Jour Avant moins jour]

8

With pleasure sir.

VolumeLendemainParClasse[DateLivraisonPrevue] is a DATE

Sum Volume = SUM(ClasseRequisitionLait[Volume])

Volume Lademain par Classe =
sum(VolumeLendemainParClasse[VolumeLendemainParClasse])

Volume Diff Jour Avant moins jour =
CALCULATE(
VolumeLendemainParClasse[Volume Lademain par Classe],
DATEADD(
VolumeLendemainParClasse[DateLivraisonPrevue],
-1,
DAY
)
) - CALCULATE(
VolumeLendemainParClasse[Volume Lademain par Classe]
)

9

Ok Greg I used your hiding trick and that’s solves my problem.

Moving to other issues

Thanks again for this trick, I had never done this before.

Case closed

Eric