Problem with actuals vs budget

[cajste]

Hi,

I've built a analysis modell for actuals vs budget. My boss wants to be able to see the verification text for the actuals while still having having the budget figurrs in the same pivot. The verification text is in the fact_Actuals and because of that I get an error in the Budget column, the budget value for that account repeats for all verification text rows. How do I solve that problem?

Example below.

Thanks,
Caj

Account	AccountName	Verifacation text	Actual	Budget
4000	AAAAA	AAA	25	150
		BBB		150
		CCC		150
		DDD	25	150
		Total	50	150

<tbody>
</tbody>

 

[Logit]

.
If 'hiding' and 'unhiding' those rows relative to contents in Col D will not alter the data display, you can try this :

Place two command buttons on your worksheet. Assign one to the HidebyVal() macro and the other button to the UnHide() macro.

Option Explicit


Sub HidebyVal()
Dim i As Integer
    For i = 2 To 300  '<-- adjust this to the total number of rows on your sheet.
        If Cells(i, 4).Value = "" Then
            Cells(i, 4).EntireRow.Hidden = True
        End If
    Next
End Sub


Sub UnHide()
Dim i As Integer
'MsgBox "Unhiding"
    For i = 2 To 300   '<-- adjust this to the total number of rows on your sheet.
        If Cells(i, 4).Value = "" Then
            Cells(i, 4).EntireRow.Hidden = False
        End If
    Next i


End Sub

 

[cajste]

Thanks! Since I want to use this in Power BI I guess a macro won't solve the problem. I'm pretty sure I've seen a DAX-solution to this problem somewhere but I can't find. Anyone have any ideas?

Thanks!
Caj

 

[Logit]

I apologize for my oversight. Did not see you were working in Power BI.

Best wishes

 

[Midlander]

Take a look at:
Budget and Other Data at Different Granularities in PowerPivot - SQLBI

 

[cajste]

Thanks! Been looking at that before but did not get it to work. Solved the problem now by setting up an extra dimension table.

 

[akice]

Another potential way to solve is to do this for the Budget number:

Budget Measure := IF ( NOT( HASONEFILTER( table[Verifacation Text] ) ), sum( table[Budget Column] ) )

This will only display 'Budget' in the 'Total' row. ( or you can try ISFILTERED instead depending on your circumstances.