Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filtering messing up calculations

I've got a data table, which is created by appending using two separate sql, with the difference of timeperiod. The reason for this is that I want to analyse things before and after a certain timeperiod and as i used sql to calculate certain measurements before retreiving data to PBI, i used two sql queries. The two are appended into one table in PBI with creating a column to separate the two datasets from each other, with two values (viimeinen_12kk & historia_pl_viim12kk)

 

Now I have two tables in a dashboard, one presenting the situation before and the other after the timeperiod. Both show the count and share (percent of grand total) of count customer_key (asiakas_key) in the table. I've also created a table, which presents the difference in the shares of the table using these:

 

viim12kk_osuus = ( CALCULATE( COUNT( RFM_yhdistetty[asiakas_key] ), RFM_yhdistetty[Aikajakso] = "viimeinen_12kk" )
    / CALCULATE( COUNT( RFM_yhdistetty[asiakas_key] ), ALL(RFM_yhdistetty), RFM_yhdistetty[Aikajakso] = "viimeinen_12kk" ) )
 
yli12kk_osuus = ( CALCULATE( COUNT( RFM_yhdistetty[asiakas_key] ), RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk" )
    / CALCULATE( COUNT( RFM_yhdistetty[asiakas_key] ), ALL(RFM_yhdistetty), RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk" ) )
 
The outcome of the dirfference is presented in a table:
Osuuden erotus (<12kk vs >12kk) = 'Calculations'[viim12kk_osuus]-'Calculations'[yli12kk_osuus]
 
Now, I also want to slice the whole dashboard with a value in a column. The problem is, that when I don't use the slicer, the calculations produces the correct difference, but as soon as i use the slicer, the calculations of the differences are incorrect. Any ideas where to look for the problem?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please update the formula of measure [viim12kk_osuus] and [yli12kk_osuus] as below and check if they can return the correct results... You can find the details in the attachment.

     

    viim12kk_osuus =
(
    CALCULATE (
        COUNT ( RFM_yhdistetty[asiakas_key] ),
        RFM_yhdistetty[Aikajakso] = "viimeinen_12kk"
    )
        / CALCULATE (
            COUNT ( RFM_yhdistetty[asiakas_key] ),
            ALLSELECTED ( RFM_yhdistetty ),
            RFM_yhdistetty[Aikajakso] = "viimeinen_12kk"
        )
)​
    yli12kk_osuus =
(
    CALCULATE (
        COUNT ( RFM_yhdistetty[asiakas_key] ),
        RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk"
    )
        / CALCULATE (
            COUNT ( RFM_yhdistetty[asiakas_key] ),
            ALLSELECTED ( RFM_yhdistetty ),
            RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk"
        )
)

     

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please update the formula of measure [viim12kk_osuus] and [yli12kk_osuus] as below and check if they can return the correct results... You can find the details in the attachment.

     

    viim12kk_osuus =
(
    CALCULATE (
        COUNT ( RFM_yhdistetty[asiakas_key] ),
        RFM_yhdistetty[Aikajakso] = "viimeinen_12kk"
    )
        / CALCULATE (
            COUNT ( RFM_yhdistetty[asiakas_key] ),
            ALLSELECTED ( RFM_yhdistetty ),
            RFM_yhdistetty[Aikajakso] = "viimeinen_12kk"
        )
)​
    yli12kk_osuus =
(
    CALCULATE (
        COUNT ( RFM_yhdistetty[asiakas_key] ),
        RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk"
    )
        / CALCULATE (
            COUNT ( RFM_yhdistetty[asiakas_key] ),
            ALLSELECTED ( RFM_yhdistetty ),
            RFM_yhdistetty[Aikajakso] = "historia_pl_viim12kk"
        )
)

     

    Best Regards