Forum Discussion

phb's avatar
phb
Frequent Visitor
4 years ago

Calculate remaining budget between two tables

I have been stuck on this problem for weeks.

 

I have the following data model:

 

 

 rap_Sag is the project

rap_Sagsplanlægningslinjer is the budget for a project

rap_Sagspost is the consumption for a project

rap_Sagsopgave is for something different

 

I have to calculate the budget, consumption and remaining budget for projects. A project has a project number and a kategori (K1, K2, K3)

 

My challenge is that the budget tabel only has values for K1, hence budget should not been shown for K2, K3 as shown in the picture below.

 The matrix visualisation has the following values:

 I have a table called "Kategori" to keep track of the categories, a table called "ForbrugBudget" to keep track on where the value is coming from.

 

How to create the measure for the remaining budget while still only showing budget for K1? I tried with three measures, but I can't hide empty budget columns for K2, K3.

 

My current DAX that works for budget and comsumption is:

(#1) Sum af budget + forbrug =
VAR Budget =
    CALCULATE (
        SUMX ( 'rap_Sagsplanlægningslinje', 'rap_Sagsplanlægningslinje'[Antal] ),
        'rap_Sagsplanlægningslinje'[NN-registrering] = 0
    )
VAR Forbrug =
    CALCULATE (
        SUMX ( rap_Sagspost, rap_Sagspost[Antal] ),
        USERELATIONSHIP ( rap_Sagspost[Initialer], rap_Ressource[Nummer] )
    )
VAR Samlet = Budget + Forbrug
RETURN
    IF ( Samlet = 0, BLANK (), Samlet )

 

How do I calculate for the remaining budget?

 

Thanks in advance.

 

 

 

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  phb ,

    I created some data:

    Here are the steps you can follow：

    If you only want to display the value of K1, you can use the IF function to mark the column [ktegori], and finally put it in the Filter to set it

    1. Create measure.

    Flag =
IF(
    MAX('Table1'[Kategori])="K1",1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

    What is the remaining budget and can you express the results you want in pictures

    If it is convenient, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • phb's avatar
    phb
    Frequent Visitor
    4 years ago

    Anonymous ,

    Thanks for your reply, but it doesn't solve my problem. You can find a sample report here https://www.dropbox.com/s/h5uvmdlot3obv15/Sample%20report.pbix?dl=0 (It doesn't seem like I can upload here). 

     

    The problem is that have a mandatory column (kategori), but it should only be shown if the column has any values like the example below. Budget is not shown for K3, because it doesn't have values. 

     I tried solving it with three measures like the one below, but then the columns are always shown.

     

    I can't figure out how to calculate "Restbudget" which is just "Budget" - "Forbrug" when I can't show columns without values.