Forum Discussion

powerbinewbie23's avatar
powerbinewbie23
Frequent Visitor
2 years ago

How to show different data cells in same matrix table

Hi everyone! I wanted to find out a way to show 3 different types of data rows - X, Y and Z in the same matrix table (doesn't have to be a matrix table, it can be any table).

 

Z = X/Y for each column in the table. I have to calculate this. Both X and Y already exist in the dataset.

 

Here is variable "X", which is under column "rollup_level_6_name" in the dataset, Append1.
Table 1.

 

Here is variable "Y", which is in the column "rollup_level_4_name" in the dataset, Append1.
Table 2. 


Here is how I am currently calculating all of the different columns in the above table - 

Table 1. And then in the filter's pane of the table, selecting, "X" in rollup_level_6_name.

 

Table 2. And then in the filter's pane of the table, selecting, "Y" in rollup_level_4_name.

 

The table has to have the following headings (also see Table 1 and Table 2 for Power BI output) - 

X/Y/Z | Sep-23 Actual | Sep-23 Budget | Sep-22 Actual | Variance | Variance

I have measures to calculate each column - 

 

Table 4. CY YTD Actual =

CALCULATE(

    SUM('Append1'[ytdamount]),

    'Append1'[scenario] = "Actual",

    TREATAS(

        VALUES('Calendar'[Date]),

        'Append1'[Date]

    )

)

 

Table 4. CY YTD Budget =

CALCULATE(

    SUM('Append1'[ytdamount]),

    CONTAINSSTRING('Append1'[scenario], "B"),

    TREATAS(

        VALUES('Calendar'[Date]),

        'Append1'[Date]

    )

)

 

Table 4. PY YTD Actual =

CALCULATE(

    [1a. Current Quarter Actual],

    SAMEPERIODLASTYEAR('Calendar'[Date])

)

 

Table 4. Variance A v. B = 'Append1'[Table 4. CY YTD Actual] - 'Append1'[Table 4. CY YTD Budget]

 

Table 4. Variance % A v. B = 'Append1'[Table 4. Variance A v. B] / 'Append1'[Table 4. CY YTD Budget]

 

Table 4. Variance CY v. PY = 'Append1'[Table 4. CY YTD Actual]-'Append1'[Table 4. PY YTD Actual]

 

Table 4. Variance % CY v. PY = 'Append1'[Table 4. Variance CY v. PY]/'Append1'[Table 4. PY YTD Actual]

 

 

Problem is - I can create separate tables to show each row, but having it in 1 table will be a lot better. Data is pulling from another software. Dataset is scrubbed and shrunk to relevant columns. Any ideas? I've been at this for a week...

 

rollup_level_1_namerollup_level_2_namerollup_level_3_namerollup_level_4_namerollup_level_5_namerollup_level_6_namescenariofiscalyearQuarterfiscalmonthmonthnameytdamount
B1A2A3AA5XActual2023Q3901-Sep1
B1A2A3AA5XActual2023Q3901-Sep2
B1A2A3CC2C3Budget2023Q3901-Sep3
B1A2A3DD1D2Actual2023Q3901-Sep4
B1B2B3YNaNNaNActual2023Q3901-Sep5
B1B2B3Y2NaNNaNActual2023Q3901-Sep5
B1B2B3Y3NaNNaNActual2023Q3901-Sep5

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi powerbinewbie23 ,

    Can't it be displayed in a table visual object? What do you want the output to look like? Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    Best Regards
    Community Support Team _ Rongtie

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