Unable to Replace Column data in Matrix Using Switch

[abhifx]

Dear All

I have been trying to replicate the revenue statement in Power BI directly linked with a trial balance. However once i insert a format and link multi level data into Power BI, I am unable to replace blank cells with specific data calculated with a measure.

here is the rundown of structure

1) a Sample format data with a column to sort and a column to link with master data

2) master data to link trial with sample format

3) trial balance

Now I need to calculate Total Sale and bunch of other blanks (i have removed subtotal for obvious reasons) and then insert in the Matrix

However, Unable to get this done

Switch data = 

SWITCH(
    SELECTEDVALUE('MIS Format'[Line Item Desc]),"Total Sale",[Total Sales]
    ,[PL Amount]
)

Any help would be appreciatedplace to replace

 Any help is appreciated. There is even more issues but this breakthrough would be enough to have a start.

Thanks in advance

Link to Power Bi File

https://drive.google.com/file/d/185GF_modCuqKrP0RNCdfT34kjlxAwlAz/view?usp=drive_link

[abhifx]

hi @danextian , sorry for delayed reply. Getting in to quarterly review meetings.

So about the switch function, i am now able to get sub totals as per desired format, however, when the report is drilled down then total is lost. how to fix that?

below the witch function

Switch Subtotal = 

SWITCH(
    SELECTEDVALUE('MIS Format'[Line Item Desc]),
    "Total Sale",[Total Sales], 
    "Total Variable Cost",[Total Variable Cost],
    "Total COGS Without VC",[Total COGS Without VC],
    "Total COGS", [Total COGS],
    "Gross Profit",[Gross Profit],
    [Total Trial]

)

[abhifx]

Hi @amitchandak, was trying to follow your tutorial online however not able to achieve the same result. If you may help in this regard? 

[danextian]

Hi @abhifx

Have you tried checking whether it is really Total Sale or maybe there spaces before or after or there are more than one spaces between Total and Sale?
You can upload your pbix to Google or One Drive and share a public link.

[abhifx]

That is the problem, the total sales is also going to respective sales head instead of switching to Total Sales

[abhifx]

Hi @danextian , Thanks for the reply, I did check for white spaces, however, that doesnt seems to be the case.

Below is the link for BI File

https://drive.google.com/file/d/185GF_modCuqKrP0RNCdfT34kjlxAwlAz/view?usp=drive_link

[danextian]

Hi @abhifx ,

It returns blank for the second row because [Total Sales] is blank.

[abhifx]

I might have responded against the wrong reply. This is actually part of the problem as the total sales also get distributed among the sales heads. how to ignore the sub head and add the total? I also need the same to get percentage of each head against sales

[danextian]

Hi @abhifx ,

The problem is that MIS Form flows to a blank category in Database for MIS which of course doesn't have an equivalent in Trial. You can't expect Power BI to know which accounts in Trial should be aggregated for Total Sale.

This measure returns a value only if Line Item Desc is SALE, not Total Sale

Total Sales =
CALCULATE (
    SUM ( Trial[Value in Lac] ),
    'MIS Format'[Line Item Desc] = "SALE" 
)

You can change this behaviour by using FILTER and wrapping 'MIS Format' in ALL. Your new measure would be:

Total Sales =
CALCULATE (
    SUM ( Trial[Value in Lac] ),
    FILTER ( ALL ( 'MIS Format' ), 'MIS Format'[Line Item Desc] = "SALE" )
)

This wil be tedious as you'll have to do that for all measures that return blank when they shouldn't. Alternatively, you can modify MIS Format so all category items that should be included in each Line Item Desc has its own line. For example, Total Int and Dept would have a line for Fin Charges and another one for Depreciation.

[abhifx]

Dear @danextian , Nailed it, it makes sense too. Thanks for going through my Power BI jungle and find out the issue.

As for the alternative suggestion, I still need to do all this manually only as this format is very rigid. But that is ok, Just a one time activity. 

I have an additional query if you don't mind, I need to do two more things.

1) I want to divide each line by Total sales to get the percentage of sales as a separate column.

2) Subtract the current month from the previous month and also get the percentage variance.

[danextian]

The new Total Sales formula will give you the Total Sales value regardless of the filter applied to 'MIS Format' table so you can just use it as the denominator to get the % of sales. I would change the flow of relationship between 'MIS Format' and 'Database for MIS' to single direction though (from the former to latter)  so the latter doesn't filter the former. 

Getting the previous month's value would have been simple if you used a separate Dates table. Your month column doesn't mention which year they belong to but assuming they're from the same year, you can create a calculated table of months.

Months = 
VAR __NUMBER =
    GENERATESERIES ( 1, 12, 1 )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS ( __NUMBER, "Date", DATE ( 2024, [Value], 1 ) ),
        "Month", FORMAT ( [Date], "mmm" )
    )

Then create a one to many relationshiop from Months[Month] to Trial[Month]. You can then use PREVIOUSMONTH to get the previous month's value. Sample formula:

PreviousMonth =
CALCULATE ( [MyMeasure], PREVIOUSMONTH ( Months[Date] ) )

The difference should be simply

=
[MyMeasure] - [PreviousMonth]